英文:
How to select a sheet with a formula while sheet name change dynamically with INDIRECT
问题
I would like to set an ARRAYFORMULA or something similar in C3 which can read a sheet while name is changing dynamically with INDIRECT, so I can get the positions of each value with XMATCH:
I first tried to do this in D3:
XMATCH(A3:A;INDIRECT(B3:B&"!$1:$1"))
But 2 problems with this:
1: Length of Column A and B can change dynamically, so C has to be dynamic.
2: Not clean
In C3 I set the following formula:
ARRAYFORMULA(XMATCH(A3:A;INDIRECT(B3:B&"!$1:$1")))
and also tried:
ARRAYFORMULA(XMATCH(A3:A;query(INDIRECT(B3:B&"!$1:$1");"select *")))
You can see this works only for the first sheet name "WHISKEY.US" and it seems pretty logical.
My bet is we have to use a MAP, but the number of sheets may change, so we cannot initialize things.
英文:
I would like to set an ARRAYFORMULA or something similar in C3 which can read a sheet while name is changing dynamically with INDIRECT , so I can get the postions of each values with XMATCH :
I first tried to do this in D3:
> XMATCH(A3:A;INDIRECT(B3:B&"!$1:$1"))
But 2 problems with this :
1: Length of Column A and B can change dynamically, so C has to be dynamic.
2: Not clean
In C3 I set the following formula :
> ARRAYFORMULA(XMATCH(A3:A;INDIRECT(B3:B&"!$1:$1")))
and also tried :
> ARRAYFORMULA(XMATCH(A3:A;query(INDIRECT(B3:B&"!$1:$1");"select *")))
You can see this works only for the first sheet name "WHISKEY.US" and it seems pretty logical.
My bet is we have to use a MAP , but number of sheets may change so we cannot initialize things.
答案1
得分: 1
=map(A3:A;B3:B;lambda(a;b;if(len(a)*len(b);xmatch(a;indirect(b&"!1:1");)))
英文:
Can you give a shot at:
=map(A3:A;B3:B;lambda(a;b;if(len(a)*len(b);xmatch(a;indirect(b&"!1:1"));)))
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论