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
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.myonlinetraininghub.com/excel-indirect-function
/Anders
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.
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
Thank you for pointing that out.
