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

[Guest]

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?

 

[Chip Pearson]

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")

 

[Guest]

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.

 

[Guest]

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!

 

[Gord Dibben]

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

 

[Chip Pearson]

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.