how do i reference sheetnames in formulas based on cell refs

[rsclark]

I have 100 worksheets in a workbook numbered 1001 to 1100. In cells H3,H5,H7 are values that I need. On a new worksheet I want a table with column A showing the 1001 down to 1100. In col B I want to show the entry that is in H3 for every worksheet of the name in the A cell to the left. I want the entries from cells H5, H7 to appear in cols C & D. Presumably there is some sort of function/formula that I can create be referring to the entry in col A and just copy down the page, other than manually amending the sheet ref in every formula (100 x 4).

It seems that i need to how how to refer to a sheetname based upon a cell entry rather than specifying directly e.g. instead of saying 'sheet1'!H3 i want to say 'cell H3 of the page that is mentioned in cell A1'

hope i have explained it ok

 

[GlennUK]

Use the INDIRECT function, like

=INDIRECT(A1&"!H3")

 

[rsclark]

fantastic.

I knew ther must be a function but i'd spent ages looking for one, but would have struggled to find it.

Thanks GlennUK

 

[GlennUK]

My pleasure.