Help with Index Collect with Date Criteria
=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!!
Answers
-
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)
Darren Mullen, join the Smartsheet Guru Elite
Get my 7 Smartsheet tips here
Author of: Smartsheet Architecture Solutions
-
Thank you so much, but still doesn't work.
-
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!!
-
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!
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 463 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!