INDEXING and Validating
I have a perplexing question, that I am sure someone knows and I am missing here.
I am working on a sheet to gather data from a form. However, this form will get quite lengthily and may span multiple years. With that said this is where I am stumped.
=INDEX(COLLECT({Test Sheet 1 Range 1}, {Test Sheet 1 Range 1}, <> ""), Index@row)
This formula works great. But needs to look at 2 columns before collecting. I have a month only row and a year only row that pulls that data from the create date. What I need it to do is validate the month and year before it collects it and places the data on the correct sheet. I saw some posts about using MATCH function, but nothing was close to what I needed to figure it out.
I want to make sure that when it checks the Month and year it is polling the correct data and not duplicating something for September 2024 with September 2023 data. Thanks for any assistance.
Example.
Range 1 houses data about someone
Range 2 houses the Month
Range 3 houses the year
This is what I came up with, but since I am here, it's obvious it does not work.
=INDEX(COLLECT({Test Sheet 1 Range 1}, {Test Sheet 1 Range 2} = 9, {Test Sheet 1 Range 3} = 2023), Index@row)
Best Answer
-
Your formula is close, looks like you just need commas between your criterion range and criterion.
=INDEX(COLLECT({Test Sheet 1 Range 1}, {Test Sheet 1 Range 2}, = 9, {Test Sheet 1 Range 3}, = 2023), Index@row)
Answers
-
Your formula is close, looks like you just need commas between your criterion range and criterion.
=INDEX(COLLECT({Test Sheet 1 Range 1}, {Test Sheet 1 Range 2}, = 9, {Test Sheet 1 Range 3}, = 2023), Index@row)
-
Thank you so much, James! I knew I missing something. Now everything works as intended with my IFERROR function.
=IFERROR(INDEX(COLLECT({Test Sheet 1 Range 1}, {Test Sheet 1 Range 1}, <> "", {Test Sheet 1 Range 2}, 9, {Test Sheet 1 Range 3}, 2023), Index@row), "")
-
Your Welcome
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.5K Get Help
- 402 Global Discussions
- 213 Industry Talk
- 450 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 135 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!