May 13 2019 08:45 AM
Have a table dates across the top and items down the side.
The body of the table gets populated with "1s" which auto formats to a cell colour.
Id like to create a column that looks up when a row has a "1" in and then returns the date in the column header.
Just struggling to find what sort of formula to use for it any guidance would be appriciated
May 13 2019 09:17 AM
May 13 2019 09:22 AM
SolutionMay 13 2019 09:26 AM
I had tried using "IF" but the "1" could be in any cell across the table, and i didnt want to have to create a long formula with 20 "IF" Logical tests!
Is there a shorter way of achieving the same thing?
May 15 2019 10:54 PM
If there is more than one cell filled in a row, the formula picks up the cell furthest to the right.
This is fine in the main but just wanted to know if there was any way of changing it to pick the first cell it comes to? (furthest to the left?)
May 16 2019 09:27 AM
May 16 2019 10:42 AM
May 16 2019 11:22 AM
May 17 2019 11:08 PM
May 18 2019 09:29 AM
When dynamic arrays are released, multiple matches will be returned by
= FILTER( Date, Criterion )
To do something similar now would need
= SMALL( IF( Criterion, Date ), {1,2,3} )
The dates could also be listed in a wrapped cell using
= TEXTJOIN( CHAR(10), TRUE, IF( Criterion, TEXT(Date,"dd mmm"), "" ) )
May 18 2019 10:57 AM
May 13 2019 09:22 AM
Solution