populate data from other tabs automatically with a drop down list

Hi There,

After 3 hours of researching, I give up. So, I have a workbook named "MenuMaster.xlsx". I have two tabs. One is named "Menu", and the other is named "Store_1". In the future, I will add Store 2 and so on. On the "Menu" tab, I have a drop down list of the "Store_x" tabs. When I select a specific one, I want the data from that tab to populate into the exact same cells in the "Menu" tab. Does this make sense?

Oh, and I don't want to do any IF statements. If I keep expanding my stores, I don't want to change formulas every time. As a random example, if A1 is my drop down box cell, I drop down to "Store_1". So now i want whatever B1 in "Store_1" is to populate into B1 in "Menu".

Thank you for helping me sleep to whomever can answer this.

function INDIRECT("Store_"&row()&"!"&celladdress) will do it

hi tim,

thanks for the quick response. that would normally work, but "Store_" is part of a drop down box, so i cannot hard key it into the formula if i want this to work. if i were to do this as an IF function, it would be something like i am in cell B1 in "Menu" tab....IF(a1="Store_1",store_1!b1,"---") ---->but then i would have to add an IF function for every new store and drag/drop. I think that is not necessary, and I know that function exists. Another crack at it?

Or am i missing something...

sure, you are
=VLOOKUP(your drop down box,{store_1,store_2, so on},(1,2, so on})
or INDIRECT(your drop down box)&"!"&celladdress)

Try

in B1

=INDIRECT("'" &$A$1 &"'!"&ADDRESS(ROW(),COLUMN()))

will retrieve data from tab selected in A1 from Row/column of cell where formula is placed: in this case B1