I'm trying to use Filter function to collect data from the same ranges in multiple sheets based on match with "Parial Text " in cell "$B$3" but it seems that Filter Function doesn't work for multiple sheets while when testing the formula for each sheet individually it works smoothly. this is my formula for 2 sheets only but it gives me only the first sheet data
=FILTER(District1!B:D,(ISNUMBER(SEARCH(B3,District1!G:G)))+(ISNUMBER(SEARCH(B3,District1!I:I)))+(ISNUMBER(SEARCH(B3,District1!K:K)))+(ISNUMBER(SEARCH(B3,District1!M:M))),FILTER('Carnell'!B:D,(ISNUMBER(SEARCH(B3,'Carnell'!G:G)))+(ISNUMBER(SEARCH(B3,'Carnell'!I:I)))+(ISNUMBER(SEARCH(B3,'Carnell'!K:K)))+(ISNUMBER(SEARCH(B3,'Carnell'!M:M)))))
It's to collect data Col "B:D" from each sheet if cel "$B$3" = any cell in columns "G,I,K,M" at any sheet. All sheets has the same table and same format just different data and I need to repeat this function 7 times for 7 sheets
I've been searching long time to solve such issue but only found that it can be done with VSTACK function which is not available in my office version 2021
Is there any suggestions to do this through VBA ??
=FILTER(District1!B:D,(ISNUMBER(SEARCH(B3,District1!G:G)))+(ISNUMBER(SEARCH(B3,District1!I:I)))+(ISNUMBER(SEARCH(B3,District1!K:K)))+(ISNUMBER(SEARCH(B3,District1!M:M))),FILTER('Carnell'!B:D,(ISNUMBER(SEARCH(B3,'Carnell'!G:G)))+(ISNUMBER(SEARCH(B3,'Carnell'!I:I)))+(ISNUMBER(SEARCH(B3,'Carnell'!K:K)))+(ISNUMBER(SEARCH(B3,'Carnell'!M:M)))))
It's to collect data Col "B:D" from each sheet if cel "$B$3" = any cell in columns "G,I,K,M" at any sheet. All sheets has the same table and same format just different data and I need to repeat this function 7 times for 7 sheets
I've been searching long time to solve such issue but only found that it can be done with VSTACK function which is not available in my office version 2021
Is there any suggestions to do this through VBA ??