Using a List to change the worksheet being referenced

**easty** · 07-16-2010, 02:02 AM

Hi there,

When I reference cell A1 in another worksheet (call it Worksheet1) the formula is

=Worksheet1!A1

I want to be able to include in the above formula a reference to a list which contains the names of different worksheets in the workbook. So when you change the name in the list it changes the worksheet the cell is referencing.........

so say for example cell A1 contains the list and cell A2 contains the formula which references other worksheets in the workbook.

So somehow cell A2 is to be something like =A1!A1 which means = A1!(is the name of the worksheets, which changes when the list is changed)A1(is the cell in the worksheet being referenced)

I hope this all makes sense........

Easty

**DonkeyOte** · 07-16-2010, 02:31 AM

If the links are all internal then you can use INDIRECT

=INDIRECT("'"&$A$1&"'!A1")

Note however that INDIRECT is Volatile - see link in sig. for more info.

**easty** · 07-16-2010, 03:38 AM

cheers DonkeyOte. that works perfectly.

next question.......instead of A1 how would I include index(1:1111,1,1) to reference A1 instead of A1 itself?

**DonkeyOte** · 07-16-2010, 03:46 AM

Perhaps best to explain your overall requirements - ie what is you're looking to do ?

Presumably, given the INDEX question, you're looking to adjust the reference to A1 as you copy the formula, correct ?
(ie make cell reference dynamic also)

**easty** · 07-16-2010, 03:55 AM

i've essentially 150 different worksheets.

I want to have a central worksheet that you can change the list to return results from the chosen worksheet.

from time to time I insert columns and rows in the 150 worksheets. By using absolute cell references inserting columns/rows changes the referenced cell, so I've have used the index function to retain the desired referenced cell.

the indirect formula you gave me works well, but instead of having the absolute referenced cell i want to use the index function so I can retain the desired referenced cell following inserting columns/rows in the referenced worksheet........ these things are so hard to explain, I hope you understand what I'm getting at....thanks for your assistance!!

**DonkeyOte** · 07-16-2010, 04:04 AM

INDIRECT converts a string to a range.

It follows that given the Cell Address is in this instance a string (not a range [yet]):

"'"&A1&"'!A1"

the reference to A1 will not alter as you amend your source sheets
(for this reason it is often used as an alternative to your INDEX approach - I prefer the INDEX approach myself unless the sheet is being deleted and replaced)

Note also that INDIRECT has implicit precedent range.
Until the function calculates Excel has no idea what the precedent range for the INDIRECT is (it's stored as a string after all).
It is for this reason that INDIRECT is a Volatile function - ie recalculates all the time
(given XL doesn't know to what range the INDIRECT refers it follows that whenever you make any change to your model the INDIRECT function must recalculate "just in case")

So, back to your question... can you outline where your INDIRECT statements are to be located and to which cells they will refer ?

ie where is:

=INDIRECT("'"&$A$1&"'!A1")

located presently ?

**easty** · 07-16-2010, 05:06 AM

I think I'm with you.......

i've entered a column (column A) in the referenced worksheet and the cell in the

=indirect(""&$A$1&"!A1")

doesn't change to

=indirect(""&$A$1&"!B1")

as i suspected......it works perfectly!! problem solved.

cheers mate!!