Matching a Date from Origin to Destination Sheet

Is your {Event Report Form Range 4} referencing a date type of column, or a text column with dates listed as text?

If it's text, unfortunately you will need to search for the text by using quotes as you have there, and change it for each row/date.

However if your Range 4 column is a Date column then yes! You can simply reference a Date cell in your current sheet to look for that value:

=COUNTIFS({Event Report Form Range 1}, HAS(@cell, "Decision to Recover"), {Event Report Form Range 4}, [Date Reference]@row))

Let me know if this makes sense and works for you.

Cheers,

Genevieve

Try and INDEX(MATCH combination! 🙂

We'll INDEX the column you want to return, using the MATCHING value of the week number. Something like this:

=INDEX({Decision to Recover Column}, MATCH(Week@row, {Week Column}, 0))

You could also likely use a SUMIFS, if you would have duplicate week rows in your source sheet:

=SUMIFS({Decision to Recover Column}, {Week Column}, Week@row)

Let me know if either of these work for you!

Cheers,

Genevieve

All comments

Is your {Event Report Form Range 4} referencing a date type of column, or a text column with dates listed as text?

If it's text, unfortunately you will need to search for the text by using quotes as you have there, and change it for each row/date.

However if your Range 4 column is a Date column then yes! You can simply reference a Date cell in your current sheet to look for that value:

=COUNTIFS({Event Report Form Range 1}, HAS(@cell, "Decision to Recover"), {Event Report Form Range 4}, [Date Reference]@row))

Let me know if this makes sense and works for you.

Cheers,

Genevieve

Thanks, I don't why that did come to mind. It worked perfect.

On a similar note. If I am using WEEKNUMBER(TODAY(+1)) in one sheet to keep an updated count for the week; does the column in my origin sheet need to also use this formula, or will in recognize a manually input numerical value?

I'm glad that worked for you! 🙂

You can match an output from your WEEKNUMBER function to a manually input numerical value, yes!

Ok, well I guess I need help with that one too.

I have an origin sheet that I am trying to pull the contents of "Decision to Recover" based on the "Week"

In the destination sheet the "Week" column has formula =WEEKNUMBER(TODAY(+1)) to roll over at each new week.

I am trying to pull the value from "Decision to Recover" the corresponds to the "Week" value from the origin sheet that matches the "Week" value in the destination sheet.

I have tried multiple variations of COUNTIF formulas and get different error messages and sometimes "0" when there should be a value.

Your help is very much appreciated.

Try and INDEX(MATCH combination! 🙂

We'll INDEX the column you want to return, using the MATCHING value of the week number. Something like this:

=INDEX({Decision to Recover Column}, MATCH(Week@row, {Week Column}, 0))

You could also likely use a SUMIFS, if you would have duplicate week rows in your source sheet:

=SUMIFS({Decision to Recover Column}, {Week Column}, Week@row)

Let me know if either of these work for you!

Cheers,

Genevieve