Excel 2007 Using an Indirect reference over multiple rows

Question

In Excel 2007 I use the following formula to refer to cells in other worksheets in the same workbook:

=INDIRECT($B$2&"!A9")

B2 contains the name of the worksheet containing the target cells (I want to turn this into a template, so I don't enter the name into the formula). The formula works, but I want to use this same formula over multiple rows (A10, A11, A12 [...] A1000). I am using this same code over multiple rows but with different fields (B9, C9 and so on), with a variety of different content types (data, number, text).

An alternate version of the formula is as follows:

='Worksheetname'!A9

But again, this requires hard-coding the name of the worksheet, which is not desirable for me.

Because of the sheer volume of the rows manual editing is not an option: is there any way to alter this formula so I can easily use it over multiple rows? Preferably without the use of VBA, but that is not a requirement.

Answer 1

If your records are located in the same cells in both worksheets, then you can use something like following:

=INDIRECT($B$2&"!"&ADDRESS(ROW();COLUMN()))

IF now, then you can easily offset them using given ADDRESS function

P.S. Please be aware with INDIRECT function that it slows down calculation performance on your workbook (few functions are OK, but if you have 10,000 of them then you might see some delay)

Answer 2

If you want to use the same formulas for several columns as well you could include something like this:

=INDIRECT("'"&$B$2&"'!"&ADDRESS(MATCH($B4;INDIRECT(CONCATENATE    ($B$2;"!";"B:B"));0);MATCH(B$3;INDIRECT(CONCATENATE($D$2;"!";"A3:AR3"));0)))

B2=reference to sheet name
B4=first row for your data
B3=Column header

Answer 3

For those who want to pull all the same column of data from multiple sheet that have the same layout (and have each cell linked), it took me hours to work it out, but actually can be solved with a relatively simple way: =OFFSET(INDIRECT("'"&G$2&"'!l7"),$A6,0,1,1)

The Indirect part take you to the right sheet and right cell, G2, H2 ... has the Sheet names (be mindful of sheet name with space, you will need to use ' ', which is the case for my workbook), and L7 cell of that sheet, and I want L8, L9, ----L200 all being pulled through; Offset help you navigate the range of rows, A6 is an additional numbering row I added to move to the right row in each sheet.

Hope it help!