How do I create formula that allows the worksheet ref. to be chang

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

DMD1236 - 07 Mar 2007 01:05 GMT

Would like to allow user to be able pick amongst Multiple worksheets via
in-cell dropdown and formulas to change.

IE - what do I have to do to make

=SHEET1!D5

point to a cell and allow user tyo make it Sheet2! or Sheet 3! etc?

Reply to this Message

Chip Pearson - 07 Mar 2007 01:09 GMT

If I understand correctly, you can use the INDIRECT function for this. For
example, if cell A1 has a sheet name, the following formula will return the
value in cell C10 of the worksheet named in A1.

=INDIRECT("'"&A1&"'!C10")

> Would like to allow user to be able pick amongst Multiple worksheets via
> in-cell dropdown and formulas to change.
[quoted text clipped - 4 lines]
>
> point to a cell and allow user tyo make it Sheet2! or Sheet 3! etc?

Reply to this Message

DMD1236 - 07 Mar 2007 01:51 GMT

THANKS CHIP!!

Now I am being just greedy.....How do I allow the"C" portion to be user
defined as well? Imbed another Indirect formula??
Appreciate your help!

> If I understand correctly, you can use the INDIRECT function for this. For
> example, if cell A1 has a sheet name, the following formula will return the
[quoted text clipped - 10 lines]
> >
> > point to a cell and allow user tyo make it Sheet2! or Sheet 3! etc?

Reply to this Message

Chip Pearson - 08 Mar 2007 00:57 GMT

If A1 has the sheet name and B1 has the column letter, you can use

=INDIRECT("'"&A1&"'!"&B1&"10")

to return the value in Row 10 of the column named in B1 on the worksheet
named in A1.

> THANKS CHIP!!
>
[quoted text clipped - 19 lines]
>> >
>> > point to a cell and allow user tyo make it Sheet2! or Sheet 3! etc?

Reply to this Message

Max - 07 Mar 2007 01:19 GMT

One way to lay it out is to use INDIRECT ..

Example, in your summary sheet, you could have the cell refs listed in B2
across, say: D5, E6, K9, ... The sheetnames could be entered in A2 down, say:
Sheet1, Sheet2, ... .

Then just place in B2:
=IF(OR($A2="",B$1=""),"",INDIRECT("'"&TRIM($A2)&"'!"&TRIM(B$1)))
Copy B2 across and fill down to populate the table for the required returns
from the various sheets' cells. The user could simply change the cell refs
(in B2 across) and/or the sheetnames (in A2 down) as may be desired.

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Reply to this Message

Gord Dibben - 07 Mar 2007 02:11 GMT

You have a list of worksheets in E1:E10.

You have a DV dropdown list in A1 referencing that list.

In B1 you have this formula

=INDIRECT(A1 & "!D5")

Gord Dibben MS Excel MVP

>Would like to allow user to be able pick amongst Multiple worksheets via
>in-cell dropdown and formulas to change.
[quoted text clipped - 4 lines]
>
>point to a cell and allow user tyo make it Sheet2! or Sheet 3! etc?

Reply to this Message