Formula Assistance

[dionne77]

How can I modify the formula to only return/display data 'if' column C is blank?

Book3.xlsx
	A	B	C	D	E
1
2		All of the above
3
4
5	Dog	New	Dog 1	44754.5531944444	Test 2
6	Cat	In Progress		44750.6508796296	Test 1
7	Fish	New		44742.597037037	Test 1
8	Fox	In Progress		44754.5531944444	Test 2
9	Lion	New	Tiger	44750.6508796296	Test 3
Sheet1

Cell Formulas
Range	Formula
A5:E9	A5	=IFERROR(INDEX(Sheet2!$A$2:$E$99,AGGREGATE(15,6,(ROW(Sheet2!$B$2:$B$99)-ROW(Sheet2!$B$2)+1)/(MMULT(--(Sheet2!$B$2:$B$99={"New","In Progress"}),{1;1})*IF($B$2="All of the above",MMULT(--(Sheet2!$E$2:$E$99=TRANSPOSE(OFFSET(Sheet3!$A$1,,,COUNTA(Sheet3!$A$1:$A$4)-1,1))),ROW(OFFSET(Sheet3!$A$1,,,COUNTA(Sheet3!$A$1:$A$4)-1,1))^0),--(Sheet2!$E$2:$E$99=$B$2))),ROWS(A$5:A5)),COLUMNS($A5:A5))&"","")
Press CTRL+SHIFT+ENTER to enter array formulas.

Cells with Data Validation
Cell	Allow	Criteria
B2	List	=Sheet3!$A$1:$A$4

Book3.xlsx
	A	B	C	D	E
1
2	Dog	New	Dog 1	2022-07-12 13:16:36	Test 2
3	Cat	In Progress		2022-07-08 15:37:16	Test 1
4	Bird	Nothing	Bird 1	2022-07-02 12:32:30	Test 2
5	Fish	New		2022-06-30 14:19:44	Test 1
6	Fox	In Progress		2022-07-12 13:16:36	Test 2
7	Lion	New	Tiger	2022-07-08 15:37:16	Test 3
Sheet2

Book3.xlsx
	A
1	Test 1
2	Test 2
3	Test 3
4	All of the above
Sheet3

 

[Fluff]

How about

=IFERROR(INDEX(Sheet2!$A$2:$E$99,AGGREGATE(15,6,(ROW(Sheet2!$B$2:$B$99)-ROW(Sheet2!$B$2)+1)/(MMULT(--(Sheet2!$B$2:$B$99={"New","In Progress"}),{1;1})*IF($B$2="All of the above",MMULT(--(Sheet2!$E$2:$E$99=TRANSPOSE(OFFSET(Sheet3!$A$1,,,COUNTA(Sheet3!$A$1:$A$4)-1,1))),ROW(OFFSET(Sheet3!$A$1,,,COUNTA(Sheet3!$A$1:$A$4)-1,1))^0),--(Sheet2!$E$2:$E$99=$B$2)))/(Sheet2!$C$2:$C$99=""),ROWS(C$5:C5)),COLUMNS($A5:C5))&"","")

 

[dionne77]

Thank you for the quick reply, however the updated formula removed all data.

Book3.xlsx
	A	B	C	D	E
1
2		All of the above
3
4
5
6
7
8
9
Sheet1

Cell Formulas
Range	Formula
A5:E9	A5	=IFERROR(INDEX(Sheet2!$A$2:$E$99,AGGREGATE(15,6,(ROW(Sheet2!$B$2:$B$99)-ROW(Sheet2!$B$2)+1)/(MMULT(--(Sheet2!$B$2:$B$99={"New","In Progress"}),{1;1})*IF($B$2="All of the above",MMULT(--(Sheet2!$E$2:$E$99=TRANSPOSE(OFFSET(Sheet3!$A$1,,,COUNTA(Sheet3!$A$1:$A$4)-1,1))),ROW(OFFSET(Sheet3!$A$1,,,COUNTA(Sheet3!$A$1:$A$4)-1,1))^0),--(Sheet2!$E$2:$E$99=$B$2)))/(Sheet2!$C$2:$C$99=""),ROWS(C$5:C5)),COLUMNS($A5:C5))&"","")

Cells with Data Validation
Cell	Allow	Criteria
B2	List	=Sheet3!$A$1:$A$4

 

[Fluff]

You have the columns wrong at he end, it should be COLUMNS($A5:A5), also you will need to confirm it with Ctrl Shift Enter

 

[dionne77]

That fixed it, thank you!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[dionne77]

One last question, if I want to return data for the reverse, where column C contains any data, how would I modified (Sheet2!$C$2:$C$99="")?

IFERROR(INDEX(Sheet2!$A$2:$E$99,AGGREGATE(15,6,(ROW(Sheet2!$B$2:$B$99)-ROW(Sheet2!$B$2)+1)/(MMULT(--(Sheet2!$B$2:$B$99={"New","In Progress"}),{1;1})*IF($B$2="All of the above",MMULT(--(Sheet2!$E$2:$E$99=TRANSPOSE(OFFSET(Sheet3!$A$1,,,COUNTA(Sheet3!$A$1:$A$4)-1,1))),ROW(OFFSET(Sheet3!$A$1,,,COUNTA(Sheet3!$A$1:$A$4)-1,1))^0),--(Sheet2!$E$2:$E$99=$B$2)))/(Sheet2!$C$2:$C$99=""),ROWS(C$5:C5)),COLUMNS($A5:A5))&"","")

 

[Fluff]

Just change the = to <>

 

[dionne77]

Thank you.

 

[Fluff]

My pleasure.