creating a formula to flag a cell if 4 criteria match (two of which are dates)

Ali Simpson

I am looking to flag (during install) if the Ship & Venue Match a reference sheet as well as the Inc. Sign on Date is between the Start and End dates of reference sheet

Formula page

Reference page

I feel like I am going to need to use =IF but also an =AND formula but I can't find the right sequence

Brian_Richardson

Yep you have the right idea. When you say "match a reference sheet", what are you matching? ie are you checking to see if the ship identifier exists and if the venue name is the same on the row where it exists? Or that it just exists at all? Because in your example it looks like the ship identifier is there but the venue is slightly different. So what do you want the result to be in that example?

Similar question to the Start and End date check, what do you want the output to be? A checkbox flag? Some text that says "out of bounds", etc?

Ali Simpson

I am wanting to check if the Inc. Sign On (on formula page) is between the Start and End dates on the Reference sheet, but there are multiple rows of Start and End dates on that sheet, so the formula needs to reference only the row that has the Ship and Venue columns match from the Formula and Refence pages. The end goal if th e Inc. Sign On is between the Start and End dates then I want the cell to Flag

Brian_Richardson

@Ali Simpson I'm so sorry, I lost track of this thread and didn't reply back in Sept.

In your formula sheet, you should be able to do this formula.

The COLLECT part of the formula "collects" a list of Ship values where all the criteria in the reference sheet match the formula row (ship, venue, inc sign on is between start and end). COUNT then counts up how many matching rows were found, and if that's >0 then it checks the box.

The {Start}, {End}, etc references are inserted into this formula by clicking Reference Another Sheet in the formula popup helper while typing it out. Click that link, then browse to your reference sheet, select the appropriate column header to select the column, give it a name in the box at the top that references the column name, then click ok to insert the reference.

=IF(COUNT(COLLECT({{Ship},{Ship},Ship@row,{Venue},Venue@row,{Start},<[Inc. Sign On]@row, {End}>[Inc. Sign on]@row))>0,true)

Ali Simpson

Hey Brian! All good I understand, I'm just getting back to this sheet myself (having to update this manually until I figure out the formula)

I tried below formula but it is now flagging Every row. Not every show meets the needed parameters.

=IF(COUNT(COLLECT({Ref Sheet: Ship}, Ship@row, {Ref Sheet: Venue}, Venue@row, {Ref Sheet: Start Date}, <[Inc. Sign on]@row, {Ref Sheet: End Date} > [Inc. Sign off]@row)) > 0, true)

Brian_Richardson

Two things I think are causing the issue. The first is my mistake, there needs to be a comma after the {Ref Sheet: End Date} range. So

=IF(COUNT(COLLECT({Ref Sheet: Ship}, Ship@row, {Ref Sheet: Venue}, Venue@row, {Ref Sheet: Start Date}, <[Inc. Sign on]@row, {Ref Sheet: End Date}, > [Inc. Sign off]@row)) > 0, true)

The second is that you used Inc Sign Off instead of Inc Sign On for the last check against End Date.

The formula as I wrote it checks that Inc Sign On field lies between Start and End. As you wrote it above, it's checking for Sign On before the Start Date, and Sign Off after the End Date. That's going to give you a different result.

Ali Simpson

Ops hit the wrong one. Ok I've updated to correct formula, seen below, and it's still marking every row

=IF(COUNT(COLLECT({Ref Sheet: Ship}, Ship@row, {Ref Sheet: Venue}, Venue@row, {Ref Sheet: Start}, <[Inc. Sign on]@row, {Ref Sheet: End}, >[Inc. Sign on]@row)) > 0, true)

Brian_Richardson

You can troubleshoot by removing the IF part and just do a COUNT, see what you're getting for a result. or replace the COUNT with a JOIN to see what the actual data is that it's returning like this:

=JOIN(COLLECT({Ref Sheet: Ship}, Ship@row, {Ref Sheet: Venue}, Venue@row, {Ref Sheet: Start}, <[Inc. Sign on]@row, {Ref Sheet: End}, >[Inc. Sign on]@row),",")

Ali Simpson

Hello Brian!

Sorry just getting back to this, and it's still not working I am getting an #Incorrect Argument Set

=JOIN(COLLECT({Ref Sheet: Installs Range Ship}, Ship@row, {Ref Sheet: Installs Range Venue}, Venue@row, {Ref Sheet: Installs Range Start}, <[Inc. Sign on]@row, {Ref Sheet: Installs Range end}, >[Inc. Sign on]@row), ",")

MarceHolzhauzen

@Paul Newcome I feel like i have seen you do something like this before. Would @cell solve the problem?

Brian_Richardson

@Ali Simpson sorry I gave you a wrong formula. Collect requires the first argument to be the list of items you are collecting. Then everything else is range,criteria.

=JOIN(COLLECT({Ref Sheet: Installs Range Ship},{Ref Sheet: Installs Range Ship}, Ship@row, {Ref Sheet: Installs Range Venue}, Venue@row, {Ref Sheet: Installs Range Start}, <[Inc. Sign on]@row, {Ref Sheet: Installs Range end}, >[Inc. Sign on]@row), ",")

Brian_Richardson

In fact it looks like I made that error all the way back on the first suggestion for Count. Count was counting exactly 1 error and flagging because the count was more than 0.

Try. =IF(COUNT(COLLECT({Ref Sheet: Ship},{Ref Sheet: Ship}, Ship@row, {Ref Sheet: Venue}, Venue@row, {Ref Sheet: Start}, <[Inc. Sign on]@row, {Ref Sheet: End}, >[Inc. Sign on]@row)) > 0, true)

Paul Newcome

@MarceHolzhauzen I personally would use an IF/COUNTIFS, but @Brian_Richardson's IF/COUNT/COLLECT should work the same way.

Ali Simpson

Thank you all for your support and input, I'm currently getting a flag for every row still however.

Current Formula used

=IF(COUNT(COLLECT({Ref Sheet: Installs Range Ship}, Ship@row, {Ref Sheet: Installs Range Venue}, Venue@row, {Ref Sheet: Installs Range Start}, <[Inc. Sign on]@row, {Ref Sheet: Installs Range end}, >[Inc. Sign on]@row)) > 0, true)

I also tried this formula previously given and it's reading #incorrect Argument Set

=JOIN(COLLECT({Ref Sheet: Installs Range Ship}, Ship@row, {Ref Sheet: Installs Range Venue}, Venue@row, {Ref Sheet: Installs Range Start}, <[Inc. Sign on]@row, {Ref Sheet: Installs Range end}, >[Inc. Sign on]@row), ",")

Brian_Richardson

Please take a look at the updated formula I posted which has the first argument as the collection, instead of criteria.

Or do Paul’s suggestion, which is better, to use COUNTIFS instead.

=IF(COUNTIFS({Ref Sheet: Installs Range Ship}, Ship@row, {Ref Sheet: Installs Range Venue}, Venue@row, {Ref Sheet: Installs Range Start}, <[Inc. Sign on]@row, {Ref Sheet: Installs Range end}, >[Inc. Sign on]@row) > 0, true)

Ali Simpson

Success!!! thank you so much!!!