If/HAS statments

LLieske

I am trying to create a function where if a certain name is populated within a column and another column is today or greater the harvey ball turns red. The If/Has does not like looking at a date column. I can get the name part to work and have it ignite the Harvey Ball but, then can not get the date piece to work in order to combine them. Here is the date column I have tried which gets an incorrect argument set:

=IF(HAS([Check OUT Date]25:[Check OUT Date]100 >= TODAY()), "Red", "Green")

I feel like I should use an"AND" statement combined with a "HAS" statement for this to work. Please help.

Genevieve P.

Hi @LLieske

You are correct! You'll need to add in an AND function to tell the IF statement that 2 different criteria need to be met.

Can I clarify, are you looking to have the harvey ball change colour per row if these two criteria are present in that specific row?

Or is this a bigger picture overview, to see if the name is present anywhere in the sheet and associated with a date that is in the future, then turn just this one ball red (like in a Sheet Summary field)?

SCENARIO 1

This would be for a set up within the sheet, like this:

Here's a formula that you could put in an entire column, drag-filling down to look per row:

=IF(AND([Name Column]@row = "Name", [Check OUT Date]@row >= TODAY()), "Red", "Green")

SCENARIO 2:

If you're looking to see if those two criteria are present anywhere in the sheet, in general, you can use an MAX(COLLECT formula to return the MAX date, or the most future date, present in the sheet that is associated with that name. Then you can have this embedded in an IF statement saying that if the MAX date present is Today or in the Future, turn red.

Try this:

=IF(MAX(COLLECT([Check OUT Date]:[Check OUT Date], [Name Column]:[Name Column], "Name")) >= TODAY(), "Red", "Green")

Let me know if either of these work for you, or if I have misunderstood your question! It may be useful to see screen captures of your sheet to help further, but please block out any sensitive data.

Thanks!

Genevieve

Joseph Adams

@Genevieve P.

I have a follow up to this one.

I am looking to have a formula in one sheet look into another for some totaling purposes

Basically I want the formula to give me value X if the 2nd sheet has 2 specific values found on the row of the 1st sheet where the formula lives.

The 2 values must be on the same row. This doesnt work. I tried earlier using HAS function also with no success. {} brackets below are the 2nd sheet references.

=IF(AND({weeknumber of 2nd sheet} = weeknumber@row, {lot ID of 2nd sheet} = lot ID@row, "Value X", 0)

I feel like a well placed @cell may work.

Genevieve P.

Hi @Joseph Adams

It sounds like what you're looking for is actually an INDEX(COLLECT formula.

Here's another Community post where Paul details how to write this type of formula: Can you use 2 match criterias in an index/match formula?

Cheers,

Genevieve