EXCEL SHEET REFERENCE

[ChrisParks]

Hey all,
wondering whether any of you know if it is possible to do the following

have a drop down that has say 2 options
each option represents the name of a sheet on the current workbook

the contents of each sheet are identical, but depending on the situation, you may want to retrieve data from one sheet or the other

so for example, lets say that we want to refer to cell b12

is there a formula that would do the following?
=??(sheetname&B12)

where sheetname is the output from teh dropdown box and is text that equates to the sheet name
address and indirect do not seem to be the solution

appreciate any help you guys can give me. thanks!

 

[AnotherHiggins]

Have a look at the INDIRECT function.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[AnotherHiggins]

I should specify the syntax.

Let's say that Cell A1 has the dropdown and the options are Sheet2 and Sheet3.

If you want to see what is stored in Sheet2, cell B1, you would use this formula:

[tab]=Indirect(A1&"!B1")

So, what you are doing is basically constructing a text string that represents the cell you want to point to.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.