VLOOKUP Rejection Followup Help

SSParks

Greetings-

I have a grid that my staff enters half the data (white half below). The right half below comes from a form to another grid and gets pulled in via a VLOOKUP. On the form, the person has to select the correct slot number and that corresponds with the slot number row on the grid below.

The question and issue I'm having is in the example below if the first person selects slot number one and enters the data the V look up supplies it to the right of the grid in the yellow section however, if someone else goes and tries to reserve slot number one they get a successful form submission, but they never get any type of follow up stating slot one has been filled.

Do I have any options or formulas that would follow up if someone requests a slot that has already been taken?

Philip Robbins

@SSParks I'd be interested to see what others say about this, but while it's possible to do what you're asking, it's going to need a number of helper columns and arguably one or two separate sheets to get to what you're looking for.

May I suggest a simpler alternative, which is to embed a report on a dashboard next to the form, which shows the start and end times that are taken, grouped by date? This way people can see which slots are already booked and reduce the chances of double booking.

If you want to pursue your original idea, you'll need a way of flagging when an entry overlaps another one on the same day, but at the moment the way your start and end times are formatted you cannot perform any comparisons (>=, etc) as they are strings.

SSParks

Thank you for the time and the information. I actually have never embedded a form on a dashboard until I read your suggestion so I do agree it makes it a cleaner format having them both on the same page. Thank you for that.

Is there a better way I should be doing the time of the day? I was told to do it this way historically so it's recognizes a.m. versus p.m.

Cory Page

Good day, I am curious if the Slot # value is simple used for a unique match or if there is another use for that column? It may be useful to use a random generated value that's customized to match your look up sheet? It gives you an option for a prefix, suffix and starting point if you could simply update your sheet your pulling from to match the Auto number generator in smartsheets that should remove any risk of duplication. Technically you could simply type "Slot" in the prefix spot and clean your sheet so that the starting value is 1. that would honestly do the same thing except the submission user doesn't type anything in that field when submitting.

Cory Page

Never mind, I thought staff were using a webform to populate the left side this doesn't seem like a solution to your question. Sorry about that.

Jason Tarpinian

If you have a Row # type column, using the system column Row ID, you can build a helper formula like this

=IF(COUNT(COLLECT(Slot:Slot, Slot:Slot, Slot@row, [Row ID]:[Row ID], <[Row ID]@row)) > 0, true)

That formula will then check a box if a "Slot" appears more than once in the range above said particular row. You can then use the checkbox to trigger an automation that warns the person that Slot is already being used. There's no way directly on the form to remove a drop-down if it's used (unless you have Data Shuttle, I can expand on this more if you do).

So in the example screen shot below, somebody marked slow 2 a second time, so the "Duplicate Slot?" gets checked, automation can be triggered to "Created by" form user to tell them try again.