Unable to Drag/Copy formula which contains reference to Sheet I.D.

[utuber]

Hi all, I have a cell H306 with the following formula ='9002858'!$B$2 The '9002858' part refers to a Sheet number which I currently have to manually enter as the Sheet reference does not change when I Drag/copy into new rows . The Sheet Number 9002858, can be obtained from Cell B306 but I haven't been able to have the formula in Cell H306 reference that Number correctly when I just drag/copy the cell down as with other cells in the Row. The Sheet Number stays the same as the Cells I drag down from. Hope this makes sense and thank you in advance for any solutions.
Regards utuber

 

[Joe4]

Is this what you are looking for?

=INDIRECT("'" & B306 & "'!$B$2")

 

[utuber]

Is this what you are looking for?

=INDIRECT("'" & B306 & "'!$B$2")

That is Exactly what I wanted... thank you very much Joe4. I have not had experience with INDIRECT in the past so will have to check it out.
Thank you

 

[Joe4]

You are welcome.

Yes, if you would like to build range references dynamically in an Excel formula, you need to use INDIRECT.