Compare a Field if Column Can Contain Same Value More Than Once.

Hi All,

I'm creating a booking system and think I am running into trouble with my formula because it's possible to assign the same value in a column more than once (so the formula doesn't know which row to look at possibly). Formula below.

=IF(AND({DateCheckFlag}@row=1,[Asset Name]@row={DeviceName}@row),1,0)

In my case, I am allowing for future bookings and so need to check for date, however {DeviceName} can appear in the column more than once.

Essentially trying to say, check for this particular asset and check the date flag. The end result (on a different sheet) is a traffic light symbol saying Yes or no for the equipment being currently available.

Is this possible or is a different approach required?

Thank you.

Tags:

Best Answer

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Answer ✓

    Hi Alex,

    I read your question again. To confirm that I understand, you have a sheet with an asset name and date check flag columns. If you find the assets without a check in the date check flag, you want it to return a stop light symbol that is green signifying that the item is available. Correct?

    The formula to do that would be:

    =IF(COUNTIF({DateCheckFlag}, @cell=0,{devicename}, @cell=[Asset Name]@row)>0, "Green", "Red")

    Where {datecheckflag} is your date checkbox column and {device name} is your asset name column.

    Work?

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

Answers

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭

    Hi @AlexP ,

    You need to add a date reference of some type if the device name can occur more than once. Add a criteria with the date reference to your AND statement.

    Need more help?

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • AlexP
    AlexP ✭✭

    Hi @Mark Cronk

    Thanks for your reply - I am not quite sure I follow.

    Do you mean something like an auto-number system generated column where a new line comes into the sheet and auto-assigns a reference number (in a new column)?

    If that's the case, I'm not sure how incorporate that in - best I can think of is checking if the field is not blank? Although the following formula is unparseable - it's possible that it isn't correct.

    =IF(AND({DateCheckFlag}@row=1,[Asset Name]@row={DeviceName}@row, ISBLANK({DateReference})=false,1,0)

    Appreciate your assistance.

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Answer ✓

    Hi Alex,

    I read your question again. To confirm that I understand, you have a sheet with an asset name and date check flag columns. If you find the assets without a check in the date check flag, you want it to return a stop light symbol that is green signifying that the item is available. Correct?

    The formula to do that would be:

    =IF(COUNTIF({DateCheckFlag}, @cell=0,{devicename}, @cell=[Asset Name]@row)>0, "Green", "Red")

    Where {datecheckflag} is your date checkbox column and {device name} is your asset name column.

    Work?

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • AlexP
    AlexP ✭✭

    @Mark Cronk

    Thank you, this is very clever. Works great.

    Correct, I had to tweak this ever so slightly to use COUNTIFS and change the conditions of available or not, but this works great.

    In the end I have

    =IF(COUNTIFS({DateCheckFlag}, @cell = 1, {DeviceName}, @cell = [Asset Name]@row) > 0, "Red", "Green")

    Thanks for your help.

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭

    Glad you found a solution. Thank you for contributing to the Community.

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!