Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

EXCEL SHEET REFERENCE

Status
Not open for further replies.

ChrisParks

Technical User
Feb 27, 2007
10
US
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!

 
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.
 
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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top