CODEWITHSUNDEEP
excelexcel-formula
10101
10101

Reputation: 2412

Display corresponding values consistently if TRUE

I have list of attachments. There are 18 possible attachments and each is checked for TRUE in J column, if so then Attachment name is displayed with formula:

=IF('Other Data'!J1=TRUE;'Other Data'!K1;"")
=IF('Other Data'!J2=TRUE;'Other Data'!K2;"")
=IF('Other Data'!J3=TRUE;'Other Data'!K3;"")
=IF('Other Data'!J4=TRUE;'Other Data'!K4;"")
etc...

The problem is that if there is a lot of FALSE values, there is a lot of empty spaces. Is it possible to display TRUE values one after each other without gaps?

enter image description here

Upvotes: 0

Views: 26

Answers (1)

JvdV
JvdV

Reputation: 75870

I suppose the other option is that J values are FALSE?

If so, and you want to use formulas instead of a pivot table or powerquery for example, then:

enter image description here

Formula in M1:

=IFERROR(INDEX($K$1:$K$18,AGGREGATE(15,3,($J$1:$J$18=TRUE)*ROW($J$1:$J$18),COUNTIF($J$1:$J$18,FALSE)+ROW(A1))),"")

Drag down...

Pls never mind my Dutch Excel, WAAR=TRUE and ONWAAR=FALSE

Upvotes: 2

Related Questions