Cross Sheet Index, Match, and IF

Simon Bamford

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.

Paul Newcome

Try a MIN/COLLECT instead.

=MIN(COLLECT({Date Column}, {Person Column}, @cell = Contact@row, {Complete Column}, @cell <> 1))

Paul Newcome

Try a MIN/COLLECT instead.

=MIN(COLLECT({Date Column}, {Person Column}, @cell = Contact@row, {Complete Column}, @cell <> 1))

Simon Bamford

@Paul Newcome Thank you so much. Worked straight away when I put the references in. This is a great help.

Paul Newcome

Happy to help. 👍️