Forum Discussion

Kyle_Thompson's avatar
Kyle_Thompson
Copper Contributor
Apr 09, 2024
Solved

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?

  • Detlef_Lewin's avatar
    Detlef_Lewin
    Apr 09, 2024

    Kyle_Thompson 

    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

  • Kyle_Thompson 

    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_Thompson's avatar
    Kyle_Thompson
    Copper 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!C24

    But the "C" panel looks like this: 

    ='C1_2'!C24

     

    For 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_Lewin's avatar
      Detlef_Lewin
      Silver Contributor

      Kyle_Thompson 

      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_Thompson's avatar
        Kyle_Thompson
        Copper Contributor
        Gotcha... Yep, my other problem child is "R1, R2". Thank you!

Resources