Forum Discussion
Extracting a Sheet Name from Text Cell
I am writing a formula to reference a value on another sheet and running into an error I can't explain.
The workbook has several dozen sheets, each describing the electrical loads on one of the building's electrical panels. Some of the panels are two-section panels, which are combined together on one sheet. There's a Summary sheet which lists the panels and references a particular cell from each associated sheet (C24 in this case) which contains the sum of the electrical loads.
I am using the INDIRECT function to reference the sheet name from the Panel column using the text value in the cell. That would look like this:
=INDIRECT([@Panel]&"!$C$24")
This works for all of the panels except the two-section panels, where the panel name is something like "A1, A2". For these panels, I named the sheets in the format of A1_2.
I modified the above function to:
=INDIRECT(IF(ISNUMBER(FIND(",",[@Panel])),CONCAT(LEFT([@Panel],FIND(",",[@Panel])-1),"_",RIGHT([@Panel],1)),[@Panel])&"!C24")
Essentially, if the panel name has a comma, it converts it from "A1, A2" to A1_2. That works! Except that sometimes it doesn't....
You can see from the screen snip that my formula is working correctly for "A1, A2" and for "G1, G2", but it's not working for "C1, C2". In total, it's successful for three out of five of the two-section panels. I have tried renaming the sheets, and I've used the step-by-step evaluation to ensure that the problem cells are getting to the correct string. I'm stumped! Any ideas?
R for row and C for column are reserved. Somehow this has impact on sheet names - even when the sheet name has additional characters.
=INDIRECT(IF(ISNUMBER(FIND(",",[@Panel])),CONCAT("'",LEFT([@Panel],FIND(",",[@Panel])-1),"_",RIGHT([@Panel],1),"'"),{@Panel])&"!C24")
4 Replies
Excel accepts =A1_2!C24 but for some reason not =C1_2!C24, nor =R1_2!C24, perhaps because C and R stand for row and column in R1C1 notation. Solution: enclose the sheet name in single quotes:
='C1_2'!C24
It's best to ALWAYS do that when you're using INDIRECT.
=INDIRECT("'"&IF(ISNUMBER(FIND(",",[@Panel])),CONCAT(LEFT([@Panel],FIND(",",[@Panel])-1),"_",RIGHT([@Panel],1)),[@Panel])&"'!C24")
- Kyle_ThompsonCopper Contributor
With a little more digging, I think I found the issue, but I still don't understand the problem. I tried referencing the cells manually. The "A" panel looks like this:
=A1_2!C24But the "C" panel looks like this:
='C1_2'!C24For some reason, two of my two-section panel sheet names have single quotes around the names, and the other three don't. Why would that be, and how can I fix it?
- Detlef_LewinSilver Contributor
R for row and C for column are reserved. Somehow this has impact on sheet names - even when the sheet name has additional characters.
=INDIRECT(IF(ISNUMBER(FIND(",",[@Panel])),CONCAT("'",LEFT([@Panel],FIND(",",[@Panel])-1),"_",RIGHT([@Panel],1),"'"),{@Panel])&"!C24")- Kyle_ThompsonCopper ContributorGotcha... Yep, my other problem child is "R1, R2". Thank you!