Problem with Dynamic Sheet name and Cell Reference in formula

[Remaniak]

Hi Guys,

I have the following problem. I need to gather a huge number of tabs into just a few overview tabs. Also, the tabs to be included in each overview sheet may change.
Therefore, the sheet name needs to be dynamic. The solution I use in the formulas is the following:

= INDIRECT("'"&$A2&"'!B1")))

Although this works perfect to change the sheet name, it does not change the cell reference (B1) when I pull it across. I need to modify the cell reference manually.

Is there any simple solution to make the cell reference dynamic in this formula?

Thanks

Remaniak

 

[Jonmo1]

Try

=INDIRECT("'"&$A2&"'!"&CELL("Address",B1))

 

[Comfy]

=indirect("'"&$a2&"'!"&b2)

 

[Remaniak]

Thanks for the helps guys!

The CELL function did the trick.

Cheers