“=“ pull data from different tabs

[crashkid1982]

Hi there. I would like to build a cross-reference to different(!) (selectable via dropdown) tabs in a single file.

 

Column A in the tab “COMBINE” should distribute linked values via “=A1, =A2, =A3, …” in A based on the dropdown selection in tab “Select”. In other words if I select “Tab 1” in “Tab Select/B2”, the value should be Apple, Banana, Cherries,0,0,0…; if “Tab 2” is selected, it should pull Apple, Banana, Cherries, Plums, Grapes,0,0,0… and so on.

There is no unique key etc that I could abuse for a VLOOKUP etc.; I am envisioning a “=“ function, but cannot get the Tab names “&” chained together in the tab! link.
Any ideas?

TIA

 

[#2 · Accepted solution]

@crashkid1982 

In A1 on the Combine sheet:

=LET(v, INDIRECT("'"&SELECT!$B$2&"'!A"&ROW()), IF(v="", "", v))

Fill down.

[#3]

THANK YOU!!! That is exactly what I was looking for!!