How to reference Tab #2 in Tab #1 within a formula

[AlternateFormula1]

Background -
I have an excel document with more than three dozen tabs. It is a extremely very detailed file. Tab #1 is my master tab that pulls data from all of the other dozens of tabs to perform various calculations. In A1 of Tab #1 , I used a formula to populate the tab's name so I do not have to write it in manually and search through every single tab's name. For example, if Tab #26 is called "John Michael Smith," A1 of Tab #1 - my master tab - would automatically populate A1 to show "John Michael Smith" so I do not have to.

Question -
In A2 of Tab #1 , I want to find the median for a certain range of cells found in the "John Michael Smith" tab. Right now, this is my formula: =MEDIAN('John Michael Smith'!B3:HE3). If I want to change the formula to in Column A to show median, mean, mode, and a bunch of other things which I have already typed into the cells, instead of me manually writing "John Michael Smith" each time, is there a way for me to just reference A1 in Tab #1 so everything just gets populated automatically?

Goal -
I want my formula to look something like this:
Cell A1 =MID(CELL("filename",'John Michael Smith'!A2),FIND("]",CELL("filename",'John Michael Smith'!A2))+1,256)
Cell A2 =MEDIAN('John Michael Smith'!B3:HE3)
Cell A3 =AVG('John Michael Smith'!B3:HE3)
Cell A4 =MIN('John Michael Smith'!B3:HE3)
Cell A5 =MAX('John Michael Smith'!B3:HE3)

... but instead of me having to write in the tab name in cell A2 and so on, it should look like this (but actually work)
Cell A1 =MID(CELL("filename",'John Michael Smith'!A2),FIND("]",CELL("filename",'John Michael Smith'!A2))+1,256)
Cell A2 =MEDIAN('A1'!B3:HE3)
Cell A3 =AVG('A1'!B3:HE3)
Cell A4 =MIN('A1'!B3:HE3)
Cell A5 =MAX('A1'!B3:HE3)


HOW DO I MAKE THIS WORK???? ANY IDEAS?

 

[Oaktree]

Use the INDIRECT function.

=AVG("'"&A1&"'!B3:HE3")

Before A1, that's " then ' then ". Similarly, before the exclamation point, that's " then '.

 

[AlternateFormula1]

I tried your formula. The cell just generates #VALUE !

This is what I typed in: =MEDIAN("'"&B1&"'!B3:HE3")

Did I do something wrong?

 

[AlternateFormula1]

B1 has this: =MID(CELL("filename",'John Michael Smith'!A2),FIND("]",CELL("filename",'John Michael Smith'!A2))+1,256)
B2 has this: =MEDIAN("'"&B1&"'!B3:HE3")

... which should tell B2 to look in the tab labeled "John Michael Smith" and calculate what the median value is for the range B3:HE3 in that tab.


I am clueless. Need help. Thanks!!

 

[Oaktree]

Oops! I mentioned using the INDIRECT function and forgot to include it in my suggestion :/

=MEDIAN(INDIRECT("'"&B1&"'!B3:HE3"))