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.
Generating filtered dropdown lists
-
- 2StarLounger
- Posts: 141
- Joined: 21 Oct 2011, 10:08
Generating filtered dropdown lists
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78236
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Generating filtered dropdown lists
What does this mean?
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?
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
Hans
-
- 2StarLounger
- Posts: 141
- Joined: 21 Oct 2011, 10:08
Re: Generating filtered dropdown lists
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.
Many thanks and best regards,
Zk.
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.
Many thanks and best regards,
Zk.
-
- Administrator
- Posts: 78236
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Generating filtered dropdown lists
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.
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
Hans