Filter using mutiple sheets to create an array?

[MiniFav]

I am looking to colate a list of values which appear on multiple tabs in a work book.

Sheet 1 and 2 below have varying data but not all in the same columns.

Book1
	A	B	C
1	Numbers	Letters	Colours
2	123	abc	blue
3	456	def	red
4	789	ghi	yellow
Sheet1

Book1
	A	B	C	D
1	Shapes	Numbers	Letters	Colours
2	Circle	123	abc	red
3	Square	456	def	yellow
4	Triangle	789	ghi	blue
Sheet2

I would like sheet 3 to then filter the number columns from sheets 1 and 2 and pull any with the colour blue through. to show the below.

Book1
	A
1	Show Blue numbers
2	123
3	789
Sheet3

I can do this one sheet at a time using =FILTER(Sheet1!A2:A4,Sheet1!C2:C4="blue","") but struggling to get the array to then also check the second sheet.

I am looking to do this without macros, only formulas. Any assistance would be appreciated.

 

[Fluff]

Roughly how many rows of data do you have on each sheet?

 

[MiniFav]

Roughly how many rows of data do you have on each sheet?

Max 1000

 

[Fluff]

Ok, how about

=LET(a,FILTER(Sheet1!A2:A1000,Sheet1!C2:C1000="blue"),b,FILTER(Sheet2!B2:B1000,Sheet2!D2:D1000="blue"),r,ROWS(a),s,SEQUENCE(r+ROWS(b)),IF(s<=r,INDEX(a,s),INDEX(b,s-r)))

 

[MiniFav]

=LET(a,FILTER(Sheet1!A2:A1000,Sheet1!C2:C1000="blue"),b,FILTER(Sheet2!B2:B1000,Sheet2!D2:D1000="blue"),r,ROWS(a),s,SEQUENCE(r+ROWS(b)),IF(s<=r,INDEX(a,s),INDEX(b,s-r)))

Again Fluff you have helped me out! going to have to start sending crates of beer as thanks!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[MiniFav]

You're welcome & thanks for the feedback.

Sorry just a follow up, if I wanted to add more sheets, is that possible with this formula or is it limited to two sheets?

 

[Fluff]

You can add more like

=LET(a,FILTER(Sheet1!A2:A1000,Sheet1!C2:C1000="blue"),b,FILTER(Sheet2!B2:B1000,Sheet2!D2:D1000="blue"),c,FILTER(Sheet1!A2:A1000,Sheet1!C2:C1000="blue"),ra,ROWS(a),rb,ra+ROWS(b),s,SEQUENCE(rb+ROWS(c)),IF(s<=ra,INDEX(a,s),IF(s<=rb,INDEX(b,s-ra),INDEX(c,s-rb))))

just as long as you do not have too many sheets.

 

[MiniFav]

You can add more like

=LET(a,FILTER(Sheet1!A2:A1000,Sheet1!C2:C1000="blue"),b,FILTER(Sheet2!B2:B1000,Sheet2!D2:D1000="blue"),c,FILTER(Sheet1!A2:A1000,Sheet1!C2:C1000="blue"),ra,ROWS(a),rb,ra+ROWS(b),s,SEQUENCE(rb+ROWS(c)),IF(s<=ra,INDEX(a,s),IF(s<=rb,INDEX(b,s-ra),INDEX(c,s-rb))))

just as long as you do not have too many sheets.

I think the work book will be maximum of five sheets. I can understand the logic of majority of the formula so I will have a play. Thank you again!

 

[MiniFav]

Would you be able to review the below? I am getting a #calc error, assuming I am exceeding the amount of sheets?

=LET(a,FILTER('(NA) - NOT APPLICABLE'!A2:A1000,'(NA) - NOT APPLICABLE'!I2:I1000="NOT IN BASELINE, VIEW EXEMPTIONS"),b,FILTER('(CLOSED) - CLOSED-TERMINATED'!B2:B1000,'(CLOSED) - CLOSED-TERMINATED'!L2:L1000="NOT IN BASELINE, VIEW EXEMPTIONS"),c,FILTER('(OPEN) - OPEN'!A2:A1000,'(OPEN) - OPEN'!L2:L1000="NOT IN BASELINE, VIEW EXEMPTIONS"),d,FILTER('(ONE-OFF) - ONE-OFF'!A2:A1000,'(ONE-OFF) - ONE-OFF'!R2:R1000="NOT IN BASELINE, VIEW EXEMPTIONS"),e,FILTER('(REPEAT) - REPEAT'!A2:A1000,'(REPEAT) - REPEAT'!AC2:AC1000="NOT IN BASELINE, VIEW EXEMPTIONS"),ra,ROWS(a),rb,ra+ROWS(b),rc,rb+ROWS(c),rd,rc+ROWS(d),s,SEQUENCE(rd+ROWS(e)),IF(s<=ra,INDEX(a,s),IF(s<=rb,INDEX(b,s-ra),IF(s<=rc,INDEX(c,s-rb), IF(s<=rd,INDEX(d,s-rc),INDEX(e,s-rd))))))