ExcelBanter - Copy down references to several worksheets

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Copy down references to several worksheets (https://www.excelbanter.com/excel-worksheet-functions/265531-copy-down-references-several-worksheets.html)

Copy down references to several worksheets

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!

Quote:

Originally Posted by parkie (Post 958979)

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