Listbox - as time slots for given date accepted, remove those times from listbox

[sipa]

I'd like to have a listbox displaying times from 8am to 6pm with say 15 min intervals, these are allocated against a date in an adjoining column.
As the times are allocated I'd like them to not be visible in the remaining list.

I can create listboxes but don't know how to achieve the above.
Thanks for helping.

[harrisonland]

One approach may be to use a helper column... List the available time slots in one column, then create another which lists distinct values.

Then use the helper column to populate the dropdown list in the datavalidation.

Does this do it for you?

Tim

[sipa]

Hi Tim,

Thanks very much for replying.
Your solution does work. Is there a way to eliminate the helper columns ?

Even a vba solution maybe ?

[harrisonland]

You could do this in VBA and trigger it in the Worksheet_Change() event...

Please Login or Register  to view this content.

Tim

[sipa]

Hi Tim,
Thanks for the macro example, which works wonderfully in your example.

I've just realised on viewing your example that I omitted some info in my original request for help. I've attached a sample sheet to show what I mean.
In sheet "N Status" Col K are dates, for each date I'd like to have the full range of available appt times, some or all could be allocated. The appt times reside in sheet "Setup" col D.

Once again, thank you for your continued help.

[torachan]

Hi sipa,
there has to be some logic - although I fail to grasp it - why are the dates haphazardly randomly placed with gaps and no chronological order.
Given the above how and where do you decide to place your entry, this needs answering before any attempt is made to combine date with time.
torachan.

[sipa]

Hi Torachan,

Thanks for your interest.
The blanks in Col K & L on sheet "N Status" are there because no appt has been set for that row, those that are not blank are to show what it should look like when an appt has been set.
Dates are in random order just because that is how our system is designed, this cannot be changed.

We allocate a date first then set a time.

Hope that explanation is of some help.
sipa

[sipa]

I'm hoping someone might pick this up and try to help me with a solution.

I've attached an updated sample file with some notes within to try and clearly explain what I'm looking for. I know the random nature of the date order looks odd, but believe me that is how it should be.

Thanks for looking.