How do I create a cross-sheet formula to pull with multiple date dependancies?

Marissa Lowenberg

I am trying to find a function that would pull a value from one sheet to another that is dependent on different effective dates.

Basically, I have a sheet with all employees' wages and effective dates. New effective dates show up on different rows.

I have several different sheets that show each grant my organization is funded on and which employees are funded on said grants.

What I want to do is create a formula that pulls the wage that person was making during that time frame from the first sheet to the other sheets.

So far this is what I have:

I am using this formula to pull over the wage:

=IF(HAS({Position Number}, [Position Number]@row), INDEX({Pay Rate}, MATCH([Position Number]@row, {Position Number}, 0)))

An employee would have a row with the percentage they will be funded on that grant for the date ranges. I want to pull over what wage they are making at the time they are funded at that particular percentage.

Here is a screenshot from the sheet with the grant funding. For instance, this person has a new wage effective 1/1/20, but I can only figure out how to pull over the wage from the previous wage effective date.

Any advice?

Paul Newcome

Try something like this...

=INDEX(COLLECT({Pay Rate}, {Position Number}, [Position Number]@row, {Start Date}, @cell <= TODAY(), {End Date}, @cell >= TODAY()), 1)

Marissa Lowenberg

@Paul Newcome Thank you. However, the formula yields #INCORRECT ARGUMENT. Additionally, I need the wage to be the effective wage during the date ranges on the grant sheet, not just what is current....

For instance, this person is approved on the grant from 7/1/19 to 12/31/19, then again from 1/1/20 to 6/30/20. Their wage also changed on 1/1/20, so ideally, the wage would show as the old wage on the first date range, and the new wage for the second range. I hope that makes sense...?

Paul Newcome

I don't see where the error would be coming from. Can you copy/paste the formula directly from your sheet to here?

Also... can you provide screenshots with column names shown?

Marissa Lowenberg

@Paul Newcome

=INDEX(COLLECT({Pay Rate 1}, {Position Number}, [Position Number]@row, {Pay Rate Start Date}, @cell <= TODAY(), {Pay Rate End Date}, @cell >= TODAY()), 1)

Sorry its difficult to show what I have without showing confidential information. Here is the sheet with the employee effort for the grant. The Task Name column shows the employee's effort, with the total in the parent row. Each row has a different date range for the grant with the pay rate that should show the wage that employee makes during the approved date range.

Here is the source sheet that has a different row with a new pay rate, showing the Pay Rate start date.

We want to have a formula take the pay pay rate from the latter to the former that cooresponds with the pay rate start date and the range the grant is approved for. For instance, the row with the #INCORRECT ARGUMENT should have the Hourly rate from the other sheet.

I hope that makes sense. Any help is appreciated! Thank you so much!

Paul Newcome

I understand what you are trying to accomplish. What I don't understand is why that particular error is being thrown.

Let's go ahead and take care of this part though. Since you are not wanting to look at TODAY() for your dates, you are going to want o replace that with the date you want to reference like so:

=INDEX(COLLECT({Pay Rate 1}, {Position Number}, [Position Number]@row, {Pay Rate Start Date}, @cell <= [Pay Rate End Date]@row, {Pay Rate End Date}, @cell >= [Pay Rate End Date]@row), 1)

That's the easy part. The hard part is figuring out this error. When you created your cross sheet references, did you click on the column header to select the entire column, or did you manually select a range?

I have also had this happen before on other sheets when using dates in COUNTIFS. There was nothing wrong with the formula, but for whatever reason it was throwing the error. I fixed this by simply rearranging my range/criteria sets. Let's see if that works...

=INDEX(COLLECT({Pay Rate 1}, {Pay Rate Start Date}, @cell <= [Pay Rate End Date]@row, {Pay Rate End Date}, @cell >= [Pay Rate End Date]@row, {Position Number}, [Position Number]@row), 1)