Generating filtered dropdown lists

Zauberkind
2StarLounger
Posts: 141
Joined: 21 Oct 2011, 10:08

Generating filtered dropdown lists

Post by Zauberkind »

Greetings,
I've been worrying this bone for a day or two now, but I'm not getting far.
The object is to dynamically generate drop-down lists based on the values of some cells.
I have attached an entirely fictitious example as a simplified model.
The box on the left is a status list of rooms, beds per room, and occupancy.
The box in the middle is a list of customers and their room requests.
The right-hand column is a room-selection chooser.
The drop-down list should contain the numbers of all the unoccupied rooms with the specified number of beds.
I don't want to use VBA because that would require the user to trust my macros, and I wouldn't ask that.
Any solutions or insights would be welcome.
Cheers,
Zk.
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Generating filtered dropdown lists

Post by HansV »

What does this mean?

S2392.png

That Cust_1 already occupies Room 5? If so, why does this customer have a request?
Or that Cust_1 has specifically requested Room 5?
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

Zauberkind
2StarLounger
Posts: 141
Joined: 21 Oct 2011, 10:08

Re: Generating filtered dropdown lists

Post by Zauberkind »

Hi Hans,
good question; poor explanation.
...to allow the change of room assignments for Cust_1.
That's kind of a perk: it can also be done by clearing that cell and doing it over.
It would also be OK if G5 only included '7' in the selections.

In retrospect, what I had planned is impracticable, or at least overly complex.
I'll have to think about that one.
If I can achieve the goal of dynamically generating the drop-down list, I can worry about the frills later. Maybe. :evilgrin:

Many thanks and best regards,
Zk.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Generating filtered dropdown lists

Post by HansV »

I don't see a way to do what you want without VBA. The source of a data validation drop down list must be either:

A fixed string listing the items, separated by the system's list separator (comma or semicolon); it cannot be an expression resulting in such a string

or

A reference to a contiguous range in one column or one row; it can be an expression that returns such a range (with some limitations). A non-contiguous range will cause an error.
Best wishes,
Hans