How to Use Sheet Name in Dynamic Formula in Excel

Sometimes depending on the scenario, you may need to use worksheet names in the formula, providing the sheet name manually is a tedious task. To assist you to get rid of this issue in this article, I’m going to explain to you how you can use the sheet name in the dynamic formula in Excel.


Workbooks to Practice


3 Ways to Use Sheet Name in Dynamic Formula in Excel

To explain the procedures of using sheet names in a dynamic formula in the most understandable manner, I’m going to use the following 4 datasets that contain information about several sales representatives and their respective sales. In the following figures, you can see the four different sheets- Quarter 1, Quarter 2, Quarter 3, and Quarter 4.

excel sheet name in formula dynamic


1. Applying INDIRECT, IF, OR Functions to Use Sheet Name in Dynamic Formula in Excel

To do dynamic worksheet references you can use the INDIRECT function.
This function tries to evaluate text as a worksheet reference. This function helps lock the specified cell in a formula. We also can use this function within a formula because it changes cell references without changing the formula.
To get the summary of the Sales Rep in different quarters of a year. I’m going to use the INDIRECT function here.

Applying INDIRECT, IF, OR Functions to Use Sheet Name in Dynamic Formula in Excel

  • First, I selected the C4 cell to keep the Quater1 sales of Ahmed.
  • Then I used the formula where B5 is the sheet from which I will extract data.
  • After that, I selected C4 from sheet Quater1 because I will pull its data here.
  • Type the following formula in cell C4.
=INDIRECT(B4&"!C4")

Here is the extracted data from Quater1 in a new sheet.

extracted data of quarter 1

  • Now drag the formula of the first cell for the rest of the cell.

The formula will be copied to those cells and data will be extracted.

In case any of your sheets has an empty value then you can use the IF, and OR functions to avoid the error. The OR function will check if either of the sheets or values is present or not. If the value doesn’t exist it will return 0.

  • Type the following formula in cell D4.
=IF(OR(B4="",C4=""),"",INDIRECT(B4&"!C5"))

IF, OR functions

  • Let’s remove Victor’s Sales from Quater1 (quarter).

Now it is showing 0 instead of showing any error because the C5 cell is empty.

result after removing data

Let’s undo the sales value for Victor and extract data of 4 Sales Representatives using the Excel AutoFill feature.

final result after Using Sheet Name in Dynamic Formula in Excel


Similar Readings:


2. Implementing VLOOKUP Formula with Variable Sheet Name in Excel

You can use the INDIRECT function within the VLOOKUP function to extract data from multiple sheets. Here you can extract particular data or all the data from other sheets.
I have adjusted the dataset to show your examples of looking up dynamic sheet names in the formula.

Implementing VLOOKUP function to Use Sheet Name in Dynamic Formula in Excel

Now I will use the VLOOKUP function within the INDIRECT function.

  • First, I selected the lookup value B4 as I want to extract Sales data of Ahmed.
  • Then I used the INDIRECT function where I made the sheet name absolute row reference and gave the range of sheet data.
  • Apply the following formula in cell C4.
=VLOOKUP($B4,INDIRECT("'"&C$3&"'!"&"B4:C10"),2,0)

After applying the formula, I got the extracted value for Ahmed is $16,800.

  • Now, to copy the formula for the rest of the cells I dragged the formula.

All data are extracted from 4 sheets in the new sheet.

final result after applying dynamic formula


3. Applying Dynamic Formula to Refer to Another Workbook

We also can dynamically refer to cells in another workbook. First, I made a sheet where four sales representatives’ total sales have been calculated for a year.

Applying Dynamic Formula to refer to another workbook in Excel

Now, I opened another workbook named Dynamic Formula 2.xlsx and made a sheet where I put the workbook name and sheet name. Also mentioned is that I will extract the Total Sales of Ahmed, Victor, Alexander, and Rose and make a column for Total Sales and Cell Numbers.

new

  • Now, Apply the formula in the Total Sales using the INDIRECT function mentioning Workbook Name, Sheet Name, and Cell Number.
=INDIRECT("'["&B4&"]"&C4&"'!"&D4)

While referencing another workbook remember to keep open your referenced workbook. Or you can mention the referenced workbook in the formula. Then the formula will be like the following one.

=INDIRECT('[Dynamic Formula 1.xlsx]Total Sales'!D4,"")

However, I am using the first one.

After, applying the formula the extracted data is here for Ahmed as C8 contains Ahmed’s data.

  • Now you can drag the formula downward to copy the formula for the rest of the Cell Number.

Here all the mentioned Cell Numbers’ data are extracted.

Final result


Practice Section

For doing practice, we have added a Practice portion on each sheet on the right portion.

Practice section for using sheet name in dynamic formula in Excel


Conclusion

In this article, I explained the dynamic way of providing Excel sheet names in the formula. Here you will know how to use dynamic formula references for excel sheet names in formula dynamic. Hope you will find this article helpful. You are most welcome to give any kind of suggestions, feedback, or ideas. Feel free to comment down below.


Further Readings

Shamima

Shamima

Hello! Welcome to my Profile. I am Shamima Sultana Rita. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. My last educational degree was BSc and my program was Computer Science and Engineering from East West University-Bangladesh. I am a Computer Science graduate with a great interest in research and development. I love to learn new things. Data Analysis is one of my favorite fields as I love to extract patterns based on problems. Here, I'm trying to provide enriched quality content regarding Excel basics to Advanced. Always try to gather knowledge from various sources and try to make innovative solutions.

3 Comments
  1. The workbook is protected and unable to be opened. Therefore, attaching it and allowing for download still does not allow one to “walk” along side your Excel process in pulling this altogether.

    Thanks for the article, but please reload a worksheet without the passwords!

    • Hello Mark,

      We uploaded the Excel files again, you can check these files also. If you find any difficulty opening the file let us know.

      Thanks

    • Hello Mark,
      Hope you are doing well.
      Whenever I try to download this workbook it works fine without a password. To be reassured a couple of my teammates also downloaded this file they also didn’t face any difficulty.
      As I haven’t used any password for this worksheet. I really want to know what caused such issues while you downloaded the file.
      Here, I will show you what it looks like when I download the file again.

      After downloading the file Excel shows a warning message. You have to click on Enable Editing.

      Excel sheet name in formula dynamic

      Later, the downloaded file will be available to use or you can make any changes you want.

      Solution

      N.B. If this solution doesn’t work for you. Kindly sent me the screenshots of the problem.

      Thank you.

      Regards
      Shamima Sultana

Leave a reply

ExcelDemy
Logo