Reference a sheet based off current sheet name

[Excel_dan]

I have set up sheets that are created in pairs for example sheet names 0024A and 0024B, I want to have a generic equation that will reference the other sheet. I can get the other sheet name by using:

=LEFT(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255),4) & "A"

but when I add on something like

 & !$H$59

it gives an error. How would I reference the first sheet from the second no matter what the name is as long as it is the same with only the last letter changed?

Thank you.
-Dan

 

[ValuationMan]

The problem with your additional line is that the ! is not in quotes.

=LEFT(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255),4) & "A!" & $H$59

 

[Excel_dan]

When I put it in the way you have shown it is not giving me the error anymore, but it is showing "0024A!" but not what is in the cell H59 on sheet 0024A. There must be an additional issue with my syntax.

 

[Fluff]

Try
=INDIRECT(LEFT(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255),4) & "A!H59")

 

[Excel_dan]

Try
=INDIRECT(LEFT(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255),4) & "A!H59")

Worked, Thank you.

 

[Fluff]

You're welcome & thanks for the feedback