Cross Sheet Index, Match, and IF
Hi,
Thank you for coming to my help, I really hop this question makes sense.
Basically, what I want to do is take the closest date of incomplete tasks and put it into another sheet where I can run automation asking someone to update a report.
The reason I want to do it this way is because on the report you could be mentioned any number of times and instead of getting numerous emails at the same time I only want them to get one when the first date is reached for their action passing.
So onto the problem. This is the sheet I have created that I hope will collect all of the actions of people across 18 different sheets that feed into the report. I can bring across peoples dates but it is bringing dates across of actions that have already been completed.
So, the other 18 sheets look like the one below. The bottom 2 dates are for the same person. But one is complete and the other isn't. I only want the incomplete one brought through.
I am using the following formula -
=INDEX({CCA 1 Council & Committees Range 1}, MATCH(Contact@row, {CCA 1 Council & Committees Range 2}, 0))
I have tried it with a 1 and a -1 but that won't always work if that person has a lot of actions.
I tried an IF statement =IF({CCA 1 Council & Committees Range 3}, 0, INDEX({CCA 1 Council & Committees Range 1}, MATCH(Contact@row, {CCA 1 Council & Committees Range 2}, 0)))
This was if complete box is not checked then bring through the number but it gave me an error.
I hope this makes sense to you all.
If you have any of your own questions please ask.
And thank you for any help you can provide.
Best Answer
-
Try a MIN/COLLECT instead.
=MIN(COLLECT({Date Column}, {Person Column}, @cell = Contact@row, {Complete Column}, @cell <> 1))
Answers
-
Try a MIN/COLLECT instead.
=MIN(COLLECT({Date Column}, {Person Column}, @cell = Contact@row, {Complete Column}, @cell <> 1))
-
@Paul Newcome Thank you so much. Worked straight away when I put the references in. This is a great help.
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 403 Global Discussions
- 215 Industry Talk
- 455 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!