Index Collect with multiple criteria including dates.

Hudson_Miller

Greetings All -

I've got a sheet of data that contains Employees, commitments (including when they are on PTO) and the dates they start and finish those project assignments.

In the below screenshot you can see that Jane is scheduled to be on the Apex project between 4/11 and 4/14.

I'm trying to write an index collect formula that will pull the Assignment name "Apex" where Jane's employee ID exists and the date is (in this example @row is 4/12/25).

My Formula is currently structured as:

=INDEX(COLLECT({Resource Scheduling | Assignment}, {Resource Scheduling | Emp Num}, @cell = "101", {Resource Scheduling | Start}, @cell >= Date@row, {Resource Scheduling | Finish}, @cell <= Date@row), 1)

I'm getting a #Invalid Value error and would appreciate some help on the formula.

Hudson_Miller

@David011 - Thank you!!!

This got me on the right track. There were a couple of things happening I think.

1. I had some range references renamed in my sheet that were causing a conflict in my formulas. Every time I'd change a reference name, everything on other sheets would get screwy.

2. The "End Date" was a calculated value based on duration, and I think that was causing a problem because the 'value' in the cell was a formula not a date.

3. My formula had a logic error as you detected, reversing the >= and < = on the start/finish did the trick.

4. My formula was using @ cell references that were not necessary.

Simplified tables and screen shots below

The Scheduling Matrix Sheet:

The resulting personnel assignment sheet. Can obviously be elaborated with full names, rates, specialties etc. using index match's.

The final formula that worked:

=IFERROR((INDEX(COLLECT({Scheduling Matrix |Project}, {Scheduling Matrix |EID}, [101]$1, {Scheduling Matrix | Finish}, >=$Date@row, {Scheduling Matrix |Start}, <=$Date@row), 1)), "-")

David011

Try this formula:

=INDEX(COLLECT({Resource Scheduling | Assignment}, {Resource Scheduling | Emp Num}, "101", {Resource Scheduling | Start}, <= Date@row, {Resource Scheduling | Finish}, >= Date@row), 1)

I hope this helps!

Hudson_Miller

@David011 - Thank you!!!

This got me on the right track. There were a couple of things happening I think.

1. I had some range references renamed in my sheet that were causing a conflict in my formulas. Every time I'd change a reference name, everything on other sheets would get screwy.

2. The "End Date" was a calculated value based on duration, and I think that was causing a problem because the 'value' in the cell was a formula not a date.

3. My formula had a logic error as you detected, reversing the >= and < = on the start/finish did the trick.

4. My formula was using @ cell references that were not necessary.

Simplified tables and screen shots below

The Scheduling Matrix Sheet:

The resulting personnel assignment sheet. Can obviously be elaborated with full names, rates, specialties etc. using index match's.

The final formula that worked:

=IFERROR((INDEX(COLLECT({Scheduling Matrix |Project}, {Scheduling Matrix |EID}, [101]$1, {Scheduling Matrix | Finish}, >=$Date@row, {Scheduling Matrix |Start}, <=$Date@row), 1)), "-")