Forum Discussion

Frezen385's avatar
Frezen385
Copper Contributor
Aug 30, 2022

Filter rows from 4 sheets based on column value

Hi there,   I'm having a bit of trouble navigating my way around some formulas trying to get this to work and after a few hours have decided to come here seeking help. I have an excel file with ...
excel
Excel on Mac
Formulas and Functions
dscheikey's avatar
dscheikey
Bronze Contributor
Aug 30, 2022

HiFrezen385, 

here is my solution:

=LET(
ma,MAX(BYROW(B2:B5,LAMBDA(in,COUNTA(INDIRECT("'"&in&"'!A:A"))))),
c_1,CHOOSE({1,2,3,4},INDEX(INDIRECT("'"&B2&"'!A2:A"&ma&""),0),INDEX(INDIRECT("'"&B3&"'!A2:A"&ma&""),0),INDEX(INDIRECT("'"&B4&"'!A2:A"&ma&""),0),INDEX(INDIRECT("'"&B5&"'!A2:A"&ma&""),0)),
c_2,INDEX(c_1,MOD(SEQUENCE((ma-1)*4,1,0),ma-1)+1,INT(SEQUENCE((ma-1)*4,1,0)/(ma-1))+1),
s_1,CHOOSE({1,2,3,4},INDEX(INDIRECT("'"&B2&"'!B2:B"&ma&""),0),INDEX(INDIRECT("'"&B3&"'!B2:B"&ma&""),0),INDEX(INDIRECT("'"&B4&"'!B2:B"&ma&""),0),INDEX(INDIRECT("'"&B5&"'!B2:B"&ma&""),0)),
s_2,INDEX(s_1,MOD(SEQUENCE((ma-1)*4,1,0),ma-1)+1,INT(SEQUENCE((ma-1)*4,1,0)/(ma-1))+1),
FILTER(c_2,s_2="Sad",""))

Resources