Vba Macro Help -Possibly a fomula

[DaveR]

What I am trying to achieve is this; I have a 'Date of refferal' in Column H, what I want to happen it that when this is filled in, Column I then offers two days which are either the following Wednesday, or the Wednesday after that?

All help greatfully recieved.

 

[GlennUK]

Just a bit of tweeking? Although you seem to be saying that there is no summary of bookings per day held anywhere ... is that right?

 

[DaveR]

The summary of booking would simply be contained in Column J

Referral Date MACPP Panel Date Time of Panel
15-Jun-11 22-Jun-11 9:00AM
16-Jun-11 22-Jun-11 9.40AM

So in the above example if someone on a new line selected 22-Jul-11, then those dates used (9.00 and 9.40) would not be available from the pick list. (I.e. you can never book two events at the same time).

 

[GlennUK]

Yes, I get the logic ... I don't think you see the scale of the problem. You want an in-cell drop-down to be available for any particular date, and yet there are no summaries by date. Are you expecting a list to be created on the fly as someone navigates to a particular cell on a row, for the date of that row? Without having a summary somewhere I would think that's the only way ... unless someone else on here has some ideas ... I may have missed an easy solution maybe???

 

[DaveR]

As i'm playing with the concept I think I am begininf to grasp that I suspect there isn't a simply solution....

I'm currently playing with having my list of times on a different sheet (Named range) but the bit that is stumping me currently is how I link that to the specific date.

Happy to hear any other ways you may think this can be achieved.

 

[GlennUK]

The thing is, you are expecting "specific date" to be whatever date that is for the row that activecell is on.

 

[DaveR]

yep thats right!

I'm currently imagining a sheet with a long list of every wednesday for the next three years with exactly the same data!

15-6-11 22-6-11 29-6-11 8-7-11

With my list of times below each one. Following the procedure at http://www.contextures.com/xlDataVal03.html


It doesn't appear a particularly elegant solution? and it also doesn't resolve the problem of linking the date requirement into dropdown list.

Tricky isn't it!

 

[GlennUK]

I can imagine a summary of times booked per date, with corresponding blank slots per date too ... you'd have to have a defined name per date to be able get it into the dropdown. Creating that many names is easy if the referenced ranges are all the same size ... would you mind having blanks at the end of the drop-down, after the available slots?

 

[DaveR]

Blanks would be fine.

 

[GlennUK]

Say that this is an entry sheet:

Excel Workbook
	A	B	C	D
1	Referral Date	MACPP Panel Date	Time of Panel	Overlap check
2	15-Jun-11	01-Jun-11	10:20	FALSE
3	15-Jun-11	15-Jun-11	10:00	FALSE
4	15-Jun-11	15-Jun-11	09:40	FALSE
5	16-Jun-11	15-Jun-11	11:20	FALSE
6	15-Jun-11	22-Jun-11	10:00	FALSE
7	16-Jun-11	22-Jun-11	09:00	FALSE
8	15-Jun-11	22-Jun-11	09:20	FALSE
9	16-Jun-11	29-Jun-11	09:00	FALSE
10	15-Jun-11	29-Jun-11	10:40	FALSE
11	16-Jun-11	29-Jun-11	11:20	FALSE
12	15-Jun-11	06-Jul-11	11:00	FALSE
13	16-Jun-11	06-Jul-11	10:00	FALSE
14	15-Jun-11	06-Jul-11	09:40	FALSE
Sheet6

( the Overlap Check is necessary if it's possible for people to change the date without re-choosing the time )

then .... ( see next post )

 

[GlennUK]

... this would be the summary and empty slots sheet:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1		09:00 AM	09:20 AM	09:40 AM	10:00 AM	10:20 AM	10:40 AM	11:00 AM	11:20 AM	Empty Slots =
2	15-Jun-11	Empty1	Empty2	Booked	Booked	Empty3	Empty4	Empty5	Booked	Slot_15Jun11	09:00 AM	09:20 AM	10:20 AM	10:40 AM	11:00 AM
3	22-Jun-11	Booked	Booked	Empty1	Booked	Empty2	Empty3	Empty4	Empty5	Slot_22Jun11	09:40 AM	10:20 AM	10:40 AM	11:00 AM	11:20 AM
4	29-Jun-11	Booked	Empty1	Empty2	Empty3	Empty4	Booked	Empty5	Booked	Slot_29Jun11	09:20 AM	09:40 AM	10:00 AM	10:20 AM	11:00 AM
Sheet7

I couldn't get the full width into Excel Jeanie, but you get the idea.

Names have been created by selecting J2:R6 and doing Create Names ( left column ).

In the entry sheet you'd have Data Validation for the times to be:
=INDIRECT("Slot_"&TEXT(B2,"ddmmmyy"))
... adjust ref as necessary.

The only caveat that I can think of is that no text entries must appear in the time choice area of the entry sheet ... not even blanks ( "" ) like those at the end of the in-cell drop-downs.