How to Use the Sheet Name in a Dynamic Formula in Excel – 3 Methods

There are 4 datasets containing information about sales representatives and their sales: Quarter 1, Quarter 2, Quarter 3, and Quarter 4.

excel sheet name in formula dynamic


Method 1 – Applying the INDIRECT, IF, OR Functions to Use the Sheet Name in a Dynamic Formula in Excel

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

  • Select C4 cell to keep the Quater1 sales of Ahmed.
  • Use the formula ( B5 is the sheet from which data will be extracted)
  • Enter the following formula in C4.
=INDIRECT(B4&"!C4")

The extracted data from Quater1 is displayed in a new sheet.

extracted data of quarter 1

  • Drag down the Fill Handle to see the result in the rest of the cells.

 

If the sheet has an empty value, use the IF, and OR functions to avoid the error.

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

IF, OR functions

  • Remove Victor’s Sales from Quater1.

0 is displayed instead of an error because C5 is empty.

result after removing data

  • Undo the sales value for Victor and extract data from the 4 Sales Representatives using the AutoFill feature.

final result after Using Sheet Name in Dynamic Formula in Excel


Method 2 – Using the VLOOKUP Formula with a Variable Sheet Name in Excel

The dataset was adjusted.

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

 

  • Select the lookup value B4 to extract the Sales data of Ahmed.
  • Use the INDIRECT function (the sheet name has an absolute row reference).
  • Enter the following formula in C4.
=VLOOKUP($B4,INDIRECT("'"&C$3&"'!"&"B4:C10"),2,0)

The extracted value for Ahmed is $16,800.

  • Drag down the Fill Handle to see the result in the rest of the cells.

final result after applying dynamic formula


Method 4 – Applying a Dynamic Formula to Refer to Another Workbook

The sample sheet showcases the four sales representatives’ total sales in a year.

Applying Dynamic Formula to refer to another workbook in Excel

Another workbook: Dynamic Formula 2.xlsx was created with a sheet containing the workbook and sheet name. To extract the Total Sales of Ahmed, Victor, Alexander, and Rose :

new

  • Use the formula in Total Sales, using the INDIRECT function (reference Workbook Name, Sheet Name, and Cell Number).
=INDIRECT("'["&B4&"]"&C4&"'!"&D4)
  • Keep the referenced workbook open.
  • To mention the referenced workbook in the formula, use:
=INDIRECT('[Dynamic Formula 1.xlsx]Total Sales'!D4,"")

Here, the first formula will be used.

C8 showcases Ahmed’s data.

  • Drag down the Fill Handle to see the result in the rest of the cells.

This is the output.

Final result

Read More: How to Reference Worksheet Name in Formula in Excel


Practice Section

Practice here.

Practice section for using sheet name in dynamic formula in Excel


Download Practice Workbooks


<< Go Back to Reference to Another SheetCell Reference in ExcelExcel Formulas | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Shamima Sultana
Shamima Sultana

Shamima Sultana, BSc, Computer Science and Engineering, East West University, Bangladesh, has been working with the ExcelDemy project for 2 years. She has written over 75+ articles and reviewed 1000+ articles for ExcelDemy. She has also led several teams with Excel VBA and Content Development works. Currently, she is working as the Project Manager and oversees the day-to-day work, leads the services team, allocates resources to the right area, etc. Her work and learning interests vary from Microsoft... Read Full Bio

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

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo