Microsoft Excel 2003 (full product)

Hi - Is there a way of referencing a worksheet in a formula, where the worksheet name is found by referencing the value of a cell...? For example, if I have a worksheet called 28-01-2009, and I have the value 28-01-2009 in cell A2 of a different worksheet, can you do something like =A2!$E$89 (which doesn't work) to reference cell E89 in worksheet 28-01-2009?

Any help much appreciated!

Thanks in advance...

Hi, You can reference other cells, including cells in another workbook with the INDIRECT function.

The limitation is that the other workbook must be open.

INDIRECT requires an address, in your case a full

[Workbook]Worksheet!Cell addressThe address can be created with the ADDRESS function

Part of the ADDRESS function is the worksheet name.

If your data has a list of available worksheets, then you can lookup the worksheet name from the list based on the value in another cell.For example I created 3 workbooks

A.xls, B.xls and C.xls

In cells A1 to A3 I put the workbook and sheet name of the 3 workbooks.

In cell B1 is a number, in this case from 1 to 3

and in cell C1 is the following formula:

=INDIRECT(ADDRESS(1,1,4,TRUE,OFFSET(A1,B1-1,0)))

The offset function selects a value from cells A1,A2 or A3 based on the value in cell B1.For this test I put

WS-A in cell A1 in A.xls,

WS-B in cell A1 in B.xls and

WS-C in cell A1 in C.xlsThis is what it looks like:

A B C 1 [A.xls]Sheet1 3 WS-C 2 [B.xls]Sheet1 3 [C.xls]Sheet1With the address function you can use other formulas to manipulate the cell being accessed in the other worksheet.

If you need to open the referenced workbook first, then I think that a macro will be required.

Also note that the INDIRECT function is a 'volatile' function, which AFAIK recalculates every time anything changes on your worksheet. A few formulas using INDIRECT is OK, but if you had a large number, this would start to slow down Excel.

Hope this is what you were looking for.

Regards

Maybe I'm missing something in the OP, but I don't see why the ADDRESS function is required. This worked for me:

=INDIRECT("'"&A2&"'!$E$89")

Just make sure you include the single quotes enclosed by the double quotes.

Thanks to you both for your quick answers - managed to get it working just by using the INDIRECT as directed by DerbyDad.

Hi ChrisGow & DerbyDad03 Glad it's working with DerbyDad03's formula.

I thought that there was a need to reference one of several worksheets based on the value in a cell, hence the list of worksheets and the value in B1, and bringing all the parts together in 'Address', but I see that that was not what was intended.

Regards

Or, if choosing from a list isrequired, this would also work with Humar's example:=INDIRECT(INDIRECT("A"&B1)&"!A1")

There's usually more than one way to skin the proverbial feline!

DerbyDad & Humar I don't know what to say - normally when you ask a question you either don't get a reply, or the reply is completely wrong. Thanks to you both, I've not only solved my problem, but also learnt how to solve additional issues!

Once again, many thanks to you both for your quick and accurate replies. :o)

Your welcome, and thanks for the 'thank you'.

If it wasn't fun, we wouldn't answer. <g>

Ask Your Question

Weekly Poll

Do you think Google should sell budget phones in the US?

Discuss in The Lounge

Poll History