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.
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)