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

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?

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

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




"DMD1236" wrote in message
...
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?

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

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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"DMD1236" wrote:
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?

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

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!

"Chip Pearson" wrote:

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




"DMD1236" wrote in message
...
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?

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

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

On Tue, 6 Mar 2007 17:05:02 -0800, DMD1236
wrote:

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?

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

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.




"DMD1236" wrote in message
...
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!

"Chip Pearson" wrote:

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




"DMD1236" wrote in message
...
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?