Help with Index Collect with Date Criteria

PaulaB

=INDEX(COLLECT({Snapshot Status2}, {Snapshot Row ID}, [Row ID]@row), {Snapshot Last Updated}, [Column5]1)

I've got multiple data sheets set to copy rows to a Snapshot sheet every set date. I am now trying to get the status from the Snapshot Sheet to populate to another sheet so I can get a better visual display but I can't get Index/Collect formula to work. Could it be because it has got a date as a criteria? Thanks!!

Darren Mullen

@PaulaB

you need to give index a numerical index and also a closing parenthesis like. Also looks like you have a parenthesis in the weekend place. Try:

=INDEX(COLLECT({Snapshot Status2}, {Snapshot Row ID}, [Row ID]@row, {Snapshot Last Updated}, [Column5]1),1)

PaulaB

Thank you so much, but still doesn't work.

PaulaB

Thank you, the formula didn't work initially but once I changed the column to Date (previously set as text) the magic happened. Thank you!!

PaulaB

I am now trying to get it to display a symbol instead of the status but I can only index text with the colour name. I have tried changing the column settings to "Symbols" but I can only get a result with the column set to "Date". Any ideas of what else I could try? Thanks!

Georgie

Hi @PaulaB,

I tested this on my end and confirmed that if the column you’re attempting to pull the status symbol into is a symbol column with the same symbol options as the referenced symbol column, the symbol should show correctly, rather than the colour of the symbol.

That is, if you’re referencing a symbol column with the Red, Yellow, and Green (RYG) circles, then the column in which you create your INDEX(COLLECT) formula should also be set up with the same symbol option. I found that if I selected the RYG symbols with the tick, exclamation mark and cross, or the ones with the different shapes, for the column with my formula, the colour of the symbol was displayed instead.

Therefore, please check that the column properties of the referenced column and the column you’re creating the formula in match - for example, if you’re using the RYG circle symbols, the column properties should look like this:

If you’ve checked this and are still seeing issues, could you provide screenshots of your sheets including the column properties of the referenced column and the column with the formula, along with the full formula you’re using?

Hope that helps!

Georgie