Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community
SOLVED

“=“ pull data from different tabs

Copper Contributor

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

 

image001.png

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 Replies
best response confirmed by crashkid1982 (Copper Contributor)
Solution

@crashkid1982 

In A1 on the Combine sheet:

 

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

 

Fill down.

 

THANK YOU!!! That is exactly what I was looking for!!
1 best response

Accepted Solutions
best response confirmed by crashkid1982 (Copper Contributor)
Solution

@crashkid1982 

In A1 on the Combine sheet:

 

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

 

Fill down.

 

View solution in original post