Changing Sheet Names in formulas

[Deano1976]

Excel 2003

I have a cell which contains the name of a sheet.
Sheet 1 for example.

I want my formula to use this cell as it's reference to the sheet I want to extract values from. So therefore if I change the value in the the cell that has the sheet name in it the formula will also change.

e.g.

Cell A1 has Sheet1.

My formula says =Sheet1!D1

When I change Cell A1 to Sheet2 I want the formula to change to =Sheet2!D1


Is this possible?

 

[combo]

You can use INDIRECT function, build address as string (INDIRECT(A1&"!D1"))

combo

 

[Deano1976]

Ok, I've done that for the formulas in my first row - thanks.

Now I want the formula to update the cell when I use the fill down option. Currently the cell row and column in the Indirect formula remains fixed.

 

[combo]

ADDRESS, ROW functions? See lookup & reference functions in help file.

combo

 

[xlbo]

faq68-2561

For how to display the sheet name directly in the cell

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question