Reference Worksheet from cell value

Microsoft Excel 2003 (full product)
September 14, 2009 at 02:01:36
Specs: Windows XP
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...

See More: Reference Worksheet from cell value

Report •

September 14, 2009 at 05:36:04

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 address

The 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:
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.xls

This is what it looks like:

		A	B	C
1	[A.xls]Sheet1	3	WS-C
2	[B.xls]Sheet1		
3	[C.xls]Sheet1		

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


Report •

September 14, 2009 at 05:52:40
Maybe I'm missing something in the OP, but I don't see why the ADDRESS function is required.

This worked for me:


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

Report •

September 14, 2009 at 06:31:46
Thanks to you both for your quick answers - managed to get it working just by using the INDIRECT as directed by DerbyDad.

Report •

Related Solutions

September 14, 2009 at 10:38:12
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.


Report •

September 14, 2009 at 12:04:05
Or, if choosing from a list is required, this would also work with Humar's example:


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

Report •

September 15, 2009 at 01:11:26
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)

Report •

September 15, 2009 at 04:49:12
Your welcome, and thanks for the 'thank you'.

Report •

September 15, 2009 at 05:42:41
If it wasn't fun, we wouldn't answer. <g>

Report •

Ask Question