how do i reference sheetnames in formulas based on cell refs

rsclark

Board Regular
Joined
May 7, 2004
Messages
101
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
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

rsclark

Board Regular
Joined
May 7, 2004
Messages
101
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
 

Watch MrExcel Video

Forum statistics

Threads
1,114,377
Messages
5,547,587
Members
410,801
Latest member
DataMgmtAnalyst7
Top