Hi

I have an excel document with about 70 worksheets. I am trying to get some core data from these 70 worksheet into my master worksheet. All data are in the same cells, but on different worksheets.

How would a formula that can be copied down look, so that the cell reference is static, but the name of the worksheet changes automatically?

As shown below, for each row I have the name of the corresponding worksheet in column A. So instead of referencing to for example cell B17 in sheet 556153-0451 with ='556153-0451'!$B$17 I would like to insert the cell contents in row A as the worksheet name: ='A5'!$B$17, so that when I copy the formula down it becomes ='A6'!$B$17, ='A7'!$B$17 etc. Clearly this formula doesn't work, but does someone know what I can do to fix it?

Best regards!

Hi,

You can use indirect and address. Try this formula in C5 and copy it down:

=INDIRECT(ADDRESS(ROW()+12,COLUMN()-1,,,$B11))


Best regards,

Steve Powell