Validation / conditional Drop-down

[jonsi]

I'm not sure if this is possible using Excel but...

I have an Input sheet which requires (amongst other similar bits of data) Location, Name, Date, Time, Department, Equipment.

I have 10 Locations, each of which has different Departments, Equipment, Managers etc.

Location, Name Date & time are all unique inputs, the rest I want selected from Drop-downs

Location 1 has it's list of Departments and Equipment in named Ranges LONDept & LONEquip on a worksheet called LON
Location 2 has it's list of Departments and Equipment in named Ranges PARDept & PAREquip on a worksheet called PAR
Location 3 has it's list of Departments and Equipment in named Ranges FRADept & FRAEquip on a worksheet called FRA
and so on for Managers and the other stuff.

Ideally I want the value of one (cell C10) on the main input sheet (FORM)to then point all the other drop-downs to its respective sheets.

i.e.
If I select LON in Cell D10 I want the drop down in D20 to look at the Equipment list on the LON Sheet range for Equipment (LONEquip)
If I select PAR in Cell D10 I want the drop down in D20 to look at the Equipment list on the PAR Sheet range for Equipment (PAREquip)

Is this do-able ...simply?

thanks
Jonsi

hwyl
Jonsi B-)
"If an apple a day keeps the Doctor away ...why don't Daleks live in Orchards?"

 

[SkipVought]

hi,

Your workbook structure is not in accordance with best and accepted structure. Excel is not designed to work with the structure you have described.

Here's a better approach:
[tt]
1. A SINGLE TABLE with your Departments and Equipment, using 2 named ranges: Department and Equipment sorted by Department.

2. A single list of all departments, that I named DeptList

3. A Data > Validation In-Cell Drop Down for selecting a department, in a cell named SelectedDept

4. A Data > Validation In-Cell Drop Down for selecting equipment for the selected department.
[/tt]

A sample table for 1)
[pre]
Department Equipment
FRA Equip 9
FRA Equip 10
LON Equip 4
LON Equip 5
LON Equip 6
LON Equip 7
LON Equip 8
PAR Equip 1
PAR Equip 2
PAR Equip 3
[/pre]

List for 2)
[pre]
DeptList
FRA
LON
PAR
[/pre]

Formula for Data > Validation List:
[tt]
=OFFSET(Department,MATCH(SelectedDept,Department,0)-1,1,COUNTIF(Department,SelectedDept),1)
[/tt]


Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[jonsi]

Thanks for your advice Skip ...much appreciated.

Jonsi

hwyl
Jonsi B-)
"If an apple a day keeps the Doctor away ...why don't Daleks live in Orchards?"

 

[jonsi]

Thanks Skip ...If I can't do something with Word I'll try Excel

hwyl
Jonsi B-)
"If an apple a day keeps the Doctor away ...why don't Daleks live in Orchards?"