Excel Forum

sheet name references

moriel hamui

Member

Members

Power BI

Forum Posts: 24

Member Since:
May 13, 2021

Offline

October 4, 2022 - 2:36 am

I'm looking to create a summary file to have one column for each sheet in the workbook. The name of the sheet is the name of the corresponding column on the summary page. How do I write the formula to use the name of the column as the sheet to use when looking for a specific cell? Attached is the template. I've tried using Indirect, but it's not working. TIA

See Answer

Anders Sehlstedt

Eskilstuna, Sweden

VIP

Members

Trusted Members

Forum Posts: 870

Member Since:
December 7, 2016

Offline

October 4, 2022 - 8:02 am

Hello,

Indirect do work just fine, you just need to ensure you reference the sheet correct. Take a look at this blog article, it will guide you.

https://www.myonlinetraininghu.....t-function

/Anders

moriel hamui

Member

Members

Power BI

Forum Posts: 24

Member Since:
May 13, 2021

Offline

October 5, 2022 - 12:41 am

Thanks, I read the blog but I'm not understanding what went wrong. I found one error and corrected it so the Indirect function works when I spell out the name.

What if my sheet name is in another cell, can I reference a cell to generate the worksheet name?

=INDIRECT("'G4'!BE11") Isn't working but =INDIRECT(""American Fork'!BE11") Does work. The sheet name is in cell G4 and spelled exactly as the sheet name is on the tab.

Riny van Eekelen

Örnsköldsvik, Sweden

Moderator

Members

Trusted Members

Moderators

Power BI

Forum Posts: 440

Member Since:
January 31, 2022

Offline

October 5, 2022 - 4:12 am

Try this:

=INDIRECT("'"&G4&"'!BE11")

Note the single quote marks that surround the sheet name that is in G4. It's important that the entire string between the brackets returns the exact same string that you would normally get with a direct reference, i.e 'American Fork'!BE11