Multiple Sheet Check and Data Return

Courtney Caswell

Each month I have to put together a collection of data to compare from different sources. Two of the sources have older material and I am trying to find a way to not duplicate items I have already checked.

Can someone help me put together a formula that is something like the following.

Check box if Name (Sheet 1) equals Name (Sheet 2) AND Receive Date (Sheet 1) OR Receive Date (Sheet 2) is in the last 60 days.

I then pull a report to verify data based on what boxes were checked each month.

I currently have 7 sheets that have unique data. Of these only 4 can be combined (working on that now) to one location. The other two are required to be separate.

Genevieve P.

Hi @Courtney Caswell

You can use an If statement to check through your criteria. If I'm understanding you correctly, this would be your structure and instructions:

IF

received date in this row is in the last 60 days

AND

the same name exists on the other sheet

OR

If there are any rows in the other sheet where the received date is in the last 60 days, for the same name as this row

Then check the box.

Formula example:

=IF(OR(

AND([Receive Date]@row <= TODAY(), [Receive Date]@row >= TODAY(-60), COUNTIF({Name Column Sheet 1}, [Name Column]@row) > 0),

COUNTIFS({Name Column Sheet 1}, [Name Column]@row, {Receive Date Sheet 1}, <=TODAY(), {Receive Date Sheet 1}, >=TODAY(-60)) > 0),

1, 0)

Brought together:

=IF(OR(AND([Receive Date]@row <= TODAY(), [Receive Date]@row >= TODAY(-60), COUNTIF({Name Column Sheet 1}, [Name Column]@row) > 0), COUNTIFS({Name Column Sheet 1}, [Name Column]@row, {Receive Date Sheet 1}, <=TODAY(), {Receive Date Sheet 1}, >=TODAY(-60)) > 0), 1, 0)

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

Cheers,

Genevieve