Dynamic Lists in Excel

SHolst22

I created a list in Excel as described in http://www.contextures.com/xlDataVal02.html

I want to have the following structure of dependant list.

List 1

List 2: Contents dependant on List 1

List 3: Contents dependant on List 2

List 4: Contents dependant on List 3

List 5: Contents dependant on List 1

I created List 1-4 using the method described in http://www.contextures.com/xlDataVal02.html but can not work out how to have List 5 also dependant on List 1. The values in all lists will need to be entered in a excel sheet and the number of items that will appear in the list will change. How can this be done and is it best to do it in VBA.

Also I want list 5 to display 2 values side by side.

Level %

1 4

2 8

3 16

4 40

5 42

6 55

7 70

While 2 values will be displayed the level value will need to be stored in the cell where the selection was made.

If anyone can help it would be greatly appreciated

Cringing Dragon

Try naming your List 5 contents as the same names as List 2, but with an extra character (eg Fruit2 and Vegetable2 to continue with the example in your link). The data validation formula will be the same as for List 2, exept add &"2" at the end of the INDIRECT statement - eg:

INDIRECT(A2&"2")

Instead of 2, use whatever extra character(s) you added to the names.

To have 2 proper columns in the list, you probably can't do it with data validation. You can do it with a combo box (from Active X Controls, not Forms).

But you could fake 2 columns by concatenating the source data (it would look like 2 columns in the list). So the named range that the data validation is pointing to should contain formulae that concatenate other values. Eg if Level is in column A and % is in column B, then in column C have formulae like:

=A3&" "&B3

Then make column C the named range.

One drawback is that the result would be the concatenated result (eg "2 8"), and if you wanted the two separately you would need to use something like:

=LEFT(A1,FIND(" ",A1)-1)

to get the left column result (assuming there are no spaces in the left column values).

Another drawback is that if the values in the columns are of different lengths, then the columns won't be very neat. You could overcome that by varying the number of spaces based on the length of the value in the first column - ie:

=A3&REPT(" ",MAX(1,20-LEN(A3)))&B3

SHolst22

A bit of a clunky solution but it does work.

Thanks heaps

Cringing Dragon

For the 2 columns, a combo box would be neater than the data validation cludge, but as you'd already headed down the path of data validation I figured you'd prefer to stick with that.

A combo box can have multiple columns as source data (but I think you have to use the Active X controls combo box, not the Forms combo box to get that option).

suna2006

hi,

I have added multiple value selection for a column in excel sheet.

The thing is if I select the same value, more than once, it is displaying.

How to bug this I have used VB code to display multiple selection.