Dynamic drop-down lists

[etsiot]

Hi to all forum members,

I have the following tricky problem:
I want to have one person doing extensive data entry as following:
- we have a list of suppliers each providing their own equipment (many devices are common)

- the user will populate a long table with the following columns
1.vendor 2.equipment 3.category

- I want the user to first select the first item (vendor) from a drop-down list
- based on the vendor selected, when going to select the second item (equipment) the drop-down list for this column should adapt and include only the devices that this vendor provides.
then, the third item (category) should be looked up according to the device selected (then again this is the easy part)

Is this feasible without resorting to access?

Thanks in advance,

Vaghelis.

[Brian from Maui]

Originally Posted by etsiot

Hi to all forum members,

I have the following tricky problem:
I want to have one person doing extensive data entry as following:
- we have a list of suppliers each providing their own equipment (many devices are common)

- the user will populate a long table with the following columns
1.vendor 2.equipment 3.category

- I want the user to first select the first item (vendor) from a drop-down list
- based on the vendor selected, when going to select the second item (equipment) the drop-down list for this column should adapt and include only the devices that this vendor provides.
then, the third item (category) should be looked up according to the device selected (then again this is the easy part)

Is this feasible without resorting to access?

Thanks in advance,

Vaghelis.

See,

http://www.mrexcel.com/board2/viewtopic.php?p=154#154

[etsiot]

thanks for the quick one.

however... the vendor-equipment matching has to be dynamic since we are talking about many devices per vendor, and some are common to all of them.
I was thinking of whether a two-entry matrix with equipment listed in rows (R) and vendors listed in columns (C)... then if a "1" was in a specific RC cell this would mean that vendor (C) is providing device (R). this would make it convenient for somebody to modify who is providing what, and also to easily add more equipment or vendors.
something like that:
Vendor 1 Vendor 2 Vendor 3.... Vendor N
Device 1 1 1 1
Device 2 1 1
...
Device N 1 1 1

[Brian from Maui]

What do you mean by "dynamic"

The link provided explains setting up a dependant drop down menus. From your original request, each list is dependant on what is being selected.

[u0107]

Hello,

try this one.

let us say the entries in your drop down list have to be in column K

Currently there are 4 entries:

Equipment 1
Equipment 2
Equipment 3
Equipment 4

put the above in rows 3 thru 6 (note leave rows 1 and 2 free)

you can put an explanatory label in K1 (like "Equpment List")

In K2 put the following:

=COUNTIF(K3:K52,">''")

In the above I have assumed that you will have a max 50 entries. If you have more than make it a sufficiently large number like 1000 in place of the 52.

So in K2 you get a dynamically generated count of the number of entries in your drop down list

In your data validation choose List and enter the following:

=INDIRECT(CONCATENATE("$K$3:$K$",2+K2))

Hope this helps.

Uttam

[Aladin Akyurek]

Originally Posted by etsiot

thanks for the quick one.

however... the vendor-equipment matching has to be dynamic since we are talking about many devices per vendor, and some are common to all of them.
I was thinking of whether a two-entry matrix with equipment listed in rows (R) and vendors listed in columns (C)... then if a "1" was in a specific RC cell this would mean that vendor (C) is providing device (R). this would make it convenient for somebody to modify who is providing what, and also to easily add more equipment or vendors.
something like that:
Vendor 1 Vendor 2 Vendor 3.... Vendor N
Device 1 1 1 1
Device 2 1 1
...
Device N 1 1 1

The scheme described in the quoted link can be augmented with definitions using dynamic formulas such that you can easily change existing lists without any need for modifying the associated definitions.

******** ******************** ************************************************************************>

Microsoft Excel - Book6 ___Running: xl2000 : OS = Windows Windows 2000
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
A1 =
	A	B	C	D	E
1	Vendors	v1	v2	v3	v4
2	v1	d1	d1	d1	d5
3	v2	d2	d2	d4	d6
4	v3	d4	d4	d7	d7
5	v4	d5	d6	d8	*
6	*	d8	*	*	*
7	*	*	*	*	*
VendorsAndDevices *

[HtmlMaker 2.32] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.



Define Vendors as follows using Insert|Name|Define...

Name: Vendors
Definition:

=VendorsAndDevices!$A$2:INDEX(VendorsAndDevices!$A:$A,MATCH(BigStr,VendorsAndDevices!$A:$A))

where BigStr, an additional name to be defined as referring to:

=REPT("z",90)

Name: V1
Definition:

=VendorsAndDevices!$B$2:INDEX(VendorsAndDevices!$B:$B,MATCH(BigStr,VendorsAndDevices!$B:$B))

etc.

[etsiot]

Really slick!!! Thanks.
Didn't know that Excel can do that stuff...
The only shortcoming I see is that it the person doing the data entry cannot add new vendors and equipment by him/herself.

But you gave me an idea...
I will add the named ranges programmatically, by sweeping the sheet, checking for new rows and adding each time new rows, if created by the user.

Thank you all very much,

Vaghelis.

[Aladin Akyurek]

Originally Posted by etsiot

Really slick!!! Thanks.
Didn't know that Excel can do that stuff...
The only shortcoming I see is that it the person doing the data entry cannot add new vendors and equipment by him/herself.

But you gave me an idea...
I will add the named ranges programmatically, by sweeping the sheet, checking for new rows and adding each time new rows, if created by the user.

Thank you all very much,

Vaghelis.

No need for that, really. Every entry by the user in any list will be counted in by the dynamic formulas. Add a new vendor and it will be immediately in Vendors without any interference, for example.

[etsiot]

Aladin,

I tested the dynamic ranges stuff.. really elegant.
I am afraid though that I am missing something.
I want to avoid letting the user input the device names again and again, since this will definitely lead to mistakes.
The matrix I was thinking of is like that..

******** ******************** ************************************************************************>

Microsoft Excel - Book1 ___Running: xl2000 : OS = Windows 98
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
A1 =
	A	B	C	D	E	F
1	Equipment	VendorA	VendorB	VendorC	VendorD	VendorE
2	Equip1	*	*	1	*	1
3	Equip2	*	1	1	1	1
4	Equip3	1	*	1	*	*
5	Equip4	*	1	1	*	1
6	Equip5	1	1	*	1	1
Equipment Matrix *

[HtmlMaker 2.40] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

So, "1" in each column, corresponds to the devices, this particular vendor provides. The drop-down list should take therefore, only the devices where a "1" is found at the particular row.
So, vendorD provides only "Equip2" and "Equip5".
I am thinking of going VBA for that; however if this can be done using only formulas, I'd love to know...
Thanks for any replies,

Vaghelis.

[Aladin Akyurek]

Vaghelis,

You must be in love with that matrix representation. But, honestly, it is not less prone to errors than the system already described. I hope what follows discourages you to a sufficient degree that you opt for my earlier proposal.

Given the equipment matrix you posted...

( 1.) Insert a new worksheet and name it Admin.

( 2.) Activate Insert|Name|Define.

( 3.) Enter Lrow as name in the Names in Workbook box.

( 4.) Enter the following in the Refers to box:

=MATCH(REPT("z",90),'Equipment Matrix'!$A:$A)

( 5.) Click OK.

Admin

( 6.) Activate Admin.

( 7.) Create a Vendors list in A2 on.

( 8.) Activate Insert|Name|Define.

( 9.) Enter Vendors in the Name in Workbook box.

(10.) Enter the following in the Refers to box:

=Admin!$A$2:INDEX(Admin!$A:$A,MATCH(REPT("z",90),Admin!$A:$A))

(11.) Click OK.

(12.) In C1 enter:

=COUNT('Equipment Matrix'!B:B)

(13.) In C2 enter & copy down as far as needed:

=IF('Equipment Matrix'!A2<>"",IF('Equipment Matrix'!B2,RANK('Equipment Matrix'!B2,'Equipment Matrix'!$B$2:INDEX('Equipment Matrix'!B:B,Lrow))+COUNTIF('Equipment Matrix'!$B$2:B2,'Equipment Matrix'!B2)-1,""),"")

(14.) In D1 enter: VendorA

(15.) In D2 enter & copy down as far as needed:

=IF(ROW()-ROW($D$1)<=$C$1,INDEX('Equipment Matrix'!$A$2:INDEX('Equipment Matrix'!A:A,Lrow),MATCH(ROW()-ROW($D$1),$C$2:INDEX(C$1:C$6,Lrow),0)),"")

(16.) Activate Insert|Name|Define.

(17.) Enter VendorA as name in the Names in Workbook box.

(18.) Enter the following in the Refers to box:

=Admin!$D$2:INDEX('Equipment Matrix'!$I:$I,Admin!$C$1+ROW(Admin!$D$1))

(19.) Click OK.

(20.) Repeat the steps 12 to 19 for each of remaining vendors.

The exhibit below shows all of the above for VendorA and VendorB...

******** ******************** ************************************************************************>

Microsoft Excel - aaDependentDropDownLists etsios v2.xls ___Running: xl2000 : OS = Windows Windows 2000
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
C1E1C2D2E2F2C3D3E3F3C4D4E4F4C5D5E5F5C6D6E6F6 =
	A	B	C	D	E	F	G
1	Vendors	*	2	VendorA	3	VendorB	*
2	VendorA	*	*	Equip3	*	Equip2	*
3	VendorB	*	*	Equip5	1	Equip4	*
4	VendorC	*	1	*	*	Equip5	*
5	VendorD	*	*	*	2	*	*
6	VendorE	*	2	*	3	*	*
7	*	*	*	*	*	*	*
8	*	*	*	*	*	*	*
Admin *

[HtmlMaker 2.32] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

Now you can set up a Vendors dropdown list using data validation in an appropriate celland a equipment dropdown list dependent on the choice that appears in cell by using...

=INDIRECT(Cell)

as described in http://www.mrexcel.com/board2/viewtopic.php?t=40