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:
but when I add on something like
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?
=LEFT(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255),4) & "A"