Controlling Traffic Light Symbols Based on Comparison and Date Fields

AlexP

Hello everyone,

I am attempting to control the "traffic light" symbols, Green, Yellow, Red, based on a comparison of one field to another and a date range.

Essentially it's a booking system where user inputs X and requests a date range. I'm trying to make it possible to book into the future and make my status symbol change colour based on whether it is available in this present moment (Of course you could simply look at the entries against the equipment and check the date fields)

I have two sheets. One is just a register for available equipment (lets call this sheet 1) and the other sheet is where the data comes in from the user (lets call this sheet 2).

A system administrator of some kind would then 'key' in the name of the equipment (in sheet 2)

Lets assume our equipment is called "EQUIPMENT1".

The status symbol in sheet 1 should then respond after comparing EQUIPMENT1 to EQUIPMENT1 on the other sheet and take into account the date. If Today's date is outside of the date range requested then the symbol will appear Green.

I think where I am running into problems is that its possible to have more than one of the EQUIPMENT1 in the column (with different dates). I say this because if I do some testing in a single sheet, with only one entry, the formulas work fine.

Here is an example of what I am trying.

=IF(AND([AssetNameSheet1]@row={AssetNameSheet2}@row), TODAY()<{NeedByDate}, TODAY()>{ReturnDate}), "Green" , "Red")

I hope what I am trying to Achieve is clear - if further clarification is needed please let me know.

Thank you all for your help.

Bassam Khalil

Dear @AlexP

Hope you are fine, please check my post about Using smartsheet to book workspaces/meeting rooms you can use the same concept to start the design of your system and if you like i can prepare the system for you but i need you to share your sheets after you export it to excel  (Delete/replace any confidential/sensitive information before sharing).

my Email:([email protected])

AlexP

@Bassam.M Khalil

Thank you for your reply - certainly a good solution for that particular booking system but not exactly what I have in my mind.

However I'm still having trouble comparing the equipment name as it's possible for a column to have more than one entry of the same text.

=IF(AND({DateCheckFlag}@row=1,[AssetNameSheet1]@row={AssetNameSheet2}@row),1,0)

I think it is because the column AssetNameSheet2 - it is possible to have the same entry more than once.

Because it's possible to assign the same equipment for a different date.

Please let me know if there is something I can try.

Bassam Khalil

Hi @AlexP

Hope you are fine, could you please supply a screenshot for your sheet  (Delete/replace any confidential/sensitive information before sharing)

AlexP

Hi @Bassam.M Khalil

I have reached a resolution and so no further help is required, thanks!

Bassam Khalil

@AlexP

Perfect