Is there a formula I can use for flagging outstanding equipment?

Andrew Ryback

Hi everyone,

I've bulit a sheet for tracking equipment shipments for my company. This is a centralized grid that shows when equipment was sent to an end user, and when that equipment was received back from the user.

I have a drop-down select column in which we can select the asset ID. I've also got a checkbox column to indicate when the equipment has been returned.

Is there a way that I can flag when equipment is "outstanding" (meaning not returned) that would prevent me from assigning that equipment to another user?

For example, if ASSET 001 has been sent out, I can't assign ASSET 001 to another user until the initial shipment has been flagged as returned.

Ideally, I'd like not to have the option to select ASSET 001 from the dropdown list until it's been checked as returned, but I'm not sure if that's possible. If not, a simple conditional formatting rule would suffice, but I imagine I'd have to add a helper column to accomplish this?

Any suggestions?

Jason P

Hi @Andrew Ryback

Correct in you will need a helper column, could this from @Ray Lindstrom work for you?

https://community.smartsheet.com/discussion/111316/highlight-duplicate-entries

Andrew Ryback

I think this only covers part of what I'm looking to do.

I have an ASSET ID column and a RETURNED column (which is a simple checkbox). If I create a new entry into the grid and list an ASSET ID that hasn't been flagged as returned (meaning the RETURNED column is unchecked), then I want to highlight that new entry - because I'm essentially trying to ship an asset that hasn't been marked as returned.

Jason P

Hey Andrew,

Another in the office runs a duplicate look up in excel, we discussed your need as we see a use for ourselves. Took some working on and a headache or two but, we changed it to Smartsheet language and ran a test on a dummy sheet & seems to work. Columns needed. Duplicate - Alert and you already have Asset and a Returned checkbox.

Set up a Duplicate Column. insert =IF(COUNTIFS(Asset:Asset, Asset@row, Returned:Returned, 0) > 1, "Duplicate", "Unique") and set as a column formula. This searches the Asset column for a duplicate and where found & returned Unticked simply displays "Duplicate or Unique" - this column can be hidden.

Set up a Alert column. Insert =IF(AND(NOT(ISBLANK(Asset@row)), COUNTIFS(Asset:Asset, Asset@row, Returned:Returned, 0) > 1, Returned@row = 0), "Issue", "" This looks in the duplicate column on the row for the word "Issue". The word "Issue" will only appear if the returned check box is Unticked on the row. Set as column formula, you could hide this column too.

Finally Set up a conditional format rule: If Alert is Issue then apply (pick highlight) to the Asset column.

Hope it works for you..

Andrew Ryback

This worked perfectly - exactly what I was looking for. Thanks so much.

Jason P

Magic, glad I could help.