Thread: use cell content as cell address in formula

Using Excel 2003. I have a formula that obtains data from another sheet. I use several copies of this sheet within the same book and in every sheet the formula needs to point to a different row. Within the formula, could I use the content of a third cell to act as the cell reference inside the formula. The formula is ='Sheet 1'!a2. When I make a second copy of this sheet in the same book I want the formula to read ='Sheet 1'!b2. In this sheet there is a cell that has as content B2 (cell A10). Could I have the cell address used in the formula come from the content of cell A10? Each sheet has about 15 similar formulas and I may end up using over a hundred instances of this sheet. Manually changing each formula in each sheet would be tedious and time consuming.

try =indirect(a10), which will then link to the cell reference which is contained in cell a10.

Regards
Mike

Cell a10 has no references. It only has the text "b2".

Indirect is definately what you need. Just type your formula as normal.

ie ='Sheet1'!b2

Then select a cell that is your reference cell. Anycell that you are not using will do. Say cell D4

In D4 enter Sheet1!B2 - note without the = sign and without any single quotes '

Then replace the formula in the cell that currently has ='Sheet1'!b2 with =D4. The returned value will be the same - because it does the same thing.

Matt

Working with the =INDIRECT(D4). Thanks a great deal.