Retrieve Data from other sheets using dropdown list.

Hi everyone,

I am looking a solution for retrieving data from other sheet using the dropdown list.
I had attached sample worksheet.

Selector tab needs the solution.

For Cell A1 need to have dropdown menu - and the data must be the tabs name
for example: Offer1, Offer 2, Offer 3...

when selecting offer 1 need to display at Column C the data located at tab Offer 1. Same also when selecting the offer 2 and offer 3 (dropdown list.)

thanks for the help.

First make a list of all your sheetnames in one column, then select the column and type the work TabNames in the Name Box which is to the left of the Formula Bar at the top.

Then go to cell A1, then to Data|Validation and choose List... then enter in the source field: =TabNames

Then in B4 enter formula:

=INDIRECT("'"&$A$2&"'!B"&ROW(B2))

copied down.

Did you follow the first part of the instructions?

See attached.


Note: I add an error trap to the formula in case A2 is blank.

Try in B3:

=IF($A$2="","",INDIRECT("'"&$A$2&"'!"&ADDRESS(ROW(B1),COLUMN(B1))))

copied down and across,

you can hide the 0's by formatting cells as custom: 0;-0;;@

Post the question in the Programming forum, specify that you have a formula, but you want a macro... maybe add link to this thread.

And mark this thread as SOLVED. (see FAQ link in my signature)