Excel Sheet Name in Formula Dynamic (3 Approaches)

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 formula dynamically in Excel.

To explain it in the most understandable manner, I’m going to use a dataset that contains information about several sales representatives and their respective sales.

DataSheet Sample

Workbook to Practice

Sheet Name in Formula Dynamic

1.Dynamic Worksheet Reference

To do dynamic worksheet reference 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.

Dynamic worksheet reference

First, I selected the C4 cell to keep the Quater1 sales of Ahmed. Then I used the formula where B5 is the sheet from where I will extract data.

After that, I selected C4 from the sheet Quater1 because I will pull its data here.

The formula is =INDIRECT(B5&"!D4")

Using INDIRECT formula for dynamic sheet reference

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

Using INDIRECT formula for dynamic sheet reference

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.

Used Autofill to copy the formula

In case any of your sheets has an empty value then you can use the IF, and the OR function to avoid the error. 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.

The Formula is =IF(OR(B5="",C5=""),"",INDIRECT(B5&"!C5"))

Using IF and OR formula for dynamic sheet reference

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

Removed one cell value

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

Applied IF and OR formula

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

Got $ rep values using Dynamic reference


Similar Readings:


2. Lookup with Variable Sheet Name

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 of other sheets.

I have adjusted the dataset to show your examples of looking up dynamic sheet names in the formula.

Using VLOOKUP for Dynamic worksheet reference

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

First, I selected the lookup value B4 as I want to extract the Sales data of Ahmed. Then I used the INDIRECT function where I made the sheet name absolute row reference and given the range of sheet data.

The formula is =VLOOKUP($B4,INDIRECT("'"&C$3&"'!"&"B4:C10"),2,0) 

Using VLOOKUP formula for dynamic sheet reference

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

Used VLOOKUP formula

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.

Used Auto fill to copy the formula

3. Dynamic Reference 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.

Now, I opened another workbook and made a sheet where I put the workbook name and sheet name. Also mentioned that I will extract the Total Sales of Ahmed, Victor, Alexander, and Rose. Made a column for Total Sales and Cell Number.

Dynamic reference to another workbook

Now, I applied the formula in the Total Sales using the INDIRECT function mentioning Workbook Name, Sheet Name, and Cell Number.

Another workbook for dynamic rference

The formula is =INDIRECT("'["&$B$5&"]"&$C$5&"'!"&D5)

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 =INDIRECT('[Excel Sheet Name in Formula Dynamic.xlsx]Total Sales of 4 Rep'!D5,"")

I am using the first one.

Applied INDIRECT formula for dynamic reference to another book

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

Applied formula to another workbook

Now you can drag the formula downward to copy the formula for the rest of the Cell Number. Here all the mentioned Cell Number data are extracted.

Extracted data in another workbook

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 reference. Hope you will find this article helpful. You are most welcome to give any kinds of suggestions, feedback, 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.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo