Excel - validation list selection index number

[Carthesis]

Hi guys.

I've got a spreadsheet that pulls data from other sheets in the workbook depending on the values selected in 2 drop-down lists (using Data Validation -> List). One drop-down is location, and the second selects various attributes that apply to each location.

I need to be able to cycle through each location, and each set of attributes at each location - I have 8 locations and 4 attributes per location, so 32 combinations. I just don't know how to set the values of the validated lists.

Obviously I could do it by manually typing each location and attribute in the VBA code and just having the code input the text directly, but I'm wondering if there's a way to set the cell value by defining an index number - index 1 is the first entry in the list, index 2 is the second et al.

I want to try to automate this as the lists might get a lot bigger in time, and at each stage I also need to do a goal seek to solve something, and doing it by hand will be a pain.

Any ideas?

Cheers!

 

[SkipVought]

Hi,

No or minimal VBA required.

All you need is 2 lists. One list is your locations. The other list relates location to attributes; 2 columns.

http://www.contextures.com/tiptech.html

has a purely spreadsheet approch to lookup drill-down. Or you can use MS Query, using a parameter, to return the attributes for the selected location. That can be done with or without VBA.

You do NOT want 32 lists!!!!!



Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Carthesis]

Skip,

I already have 2 lists, as mentioned. One list sets the location, and the second list sets one of the attribute sets for that location.

8 locations (List 1) * 4 attributes per location (List 2) = 32 combinations.

All I want to do is be able to change the values in each of the lists automatically - without having to sit there and manually:

1) LOOP x8
'click cell - select location' # trying to get this to change automatically
2) LOOP x4
'click cell - select attribute' # trying to get this to change automatically
'alter a variable until solved'
'note down values of variables and results'
'change attribute'
3) GO TO 2)
4) DO UNTIL LOOP x4 COMPLETE
5) GO TO 1)
6) DO UNTIL LOOP x8 COMPLETE

 

[SkipVought]

[tt]
Location
FrontRoom
MiddleRoom
BackRoom
[/tt]
[tt]
Location Attributes
FrontRoom Bay Window
FrontRoom Entry Door
MiddleRoom Casement Window
MiddleRoom Interior Door
BackRoom Smoke Detector
BackRoom Beer Keg
BackRoom Negotiation Table
[/tt]
Process.

1. Select a location
2. Run query to return location atticbutes.
Exa: BackRoom selected in Data > Validation in A1

Select [Attribute]
From [YourTable$]
Where [Location] = ?

The ? is supplied by the value in A1.

SIMPLE!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]