Reference a sheet based off current sheet name

Excel_dan

New Member
Joined
Jan 18, 2017
Messages
11
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:
Code:
=LEFT(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255),4) & "A"
but when I add on something like
Code:
 & !$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
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

ValuationMan

New Member
Joined
Jun 30, 2017
Messages
22
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

New Member
Joined
Jan 18, 2017
Messages
11
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,641
Office Version
  1. 365
Platform
  1. Windows
Try
=INDIRECT(LEFT(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255),4) & "A!H59")
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,641
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,109,492
Messages
5,529,181
Members
409,856
Latest member
MAO
Top