Create Dependent and Indirect lists from non-adjacent cells

Attached 160213 Accomodation.xlsx is an extract from an Order Database where records are added daily from a User Form.

Each record is based on the Unique Job No, in Col A. Col B shows the client name. Jobs may cover one or two sites, which are entered in Cols E and L. Some sites entail overnight accommodation, and those details are entered in Cols G and N.

End User wants to use the records to "automate" the Time sheets created weekly for each employee,

1. Select Job No from Drop List in D17.

2. That needs to "populate" a Dependent DropList in E17 listing the site(s) associated with the job,

3. F17 then needs to show the hotel used for whatever was selected in E17.

Example
User selects "Job 9" in D17.
E17 Droplist should show "Site 13" and "Site 14".
User selects "Site 13" - F17 will be blank (as no hotel needed).
User selects "Site 14" - F17 should show "Hotel 5"

Hope someone can see wood for trees on this!

Ochimus

Hi
See the file 160213 Accomodation.xlsx

José,

Many thanks for the prompt response and intriguing solution. Don't recall seeing "function Number, Options and Array" in a formula before, so I'll have to look into that approach.

Tony,

I must have missed something, because I can't see any link between my question and the thread you highlighted? Value some clarification.

Ochimus

Tony,

Understood. Can now mark the thread as "solved".

Ochimus

Originally Posted by Tony Valko

The link was directed to Jose.

The link topic is about posts that contain only a file with no explanation of what's in the file.

Thanks for the reminder. I read carefully the information and I agree with XOR LX.
In this case, the number of questions asked and the kind of answers that I thought were necessary, led me to put everything in the file and does not explain anything. Non happen again, except for distraction.
Best regards,
José Augusto