Call List of values based on Selection - Validation

[Stormseed]

I have 2 Fields (Columns) and I have implemented Validation Lists in both these 2 columns.

I have around 10 values in Column A which are available in the List and have around 50 values in Column B in the List.

In other words, one of the five values or items can be selected for a single and unique value in Column A.

I want some serious help to understand and learn something new.

Ok, is it possible to call one of these 5 values in Column B depending upon the selection of the value in Column A ? Both the columns have validation lists.

Can I do this via normal Excel or do I need to use VBA ?and how do I get going ?

thank you very much for your time and efforts. Please help.

 

[Aladin Akyurek]

Mr. Aladin,

I am experiencing a small issue here:

I am making a provision of extra blank rows in all the columns and hereby changing the range of the Named Ranges to a few extra blank cells. However, this change is being reflected as "blanks" in the dropdown validation lists.

Is there any way I can choose to hiding these blank rows in the lists ?

thanks again for your kind help.

If you are on Excel 2003 or beyond, convert the relevant ranges into lists by means of Data|List|Create List.

Example...

Current range for List1 is A2:A10.

A1: List1 (in bold and italic).

Select A1:A10.
Activate Data|List|Create List.
Select A2:A10, go the Name Box on the Formula Bar, type List1, and hit enter.

You can now expand/shrink List1 at will: List1 will adjust itself automatically.

 

[Stormseed]

Mr. Aladin,

Sir, you are always there for everyone the moment you are needed. I appreciate this courteous nature of yours.

However, back to the topic, I am on Excel 2000. Is there any other way to avoid the blanks while on Excel 2000 ?

Mr. Glenn,

thank you for reading, sir.

In the near future, the data can have additional departments in the company...and there can be an increase or a decrease in the number of sub-departments too...hence that provision of extra blanks rows in the Named Range so as to make room for the new Departments. If I do not do this then I will have to create the entire "Named Range" part again.

 

[Aladin Akyurek]

...
However, back to the topic, I am on Excel 2000. Is there any other way to avoid the blanks while on Excel 2000 ? ...

You can work with dynamic ranges, crafted in a particular way for data validation purposes...

Example (not all sublists are shown)...

DependentListsAsDynRanges v2a.xls
	A	B	C	D	E	F	G	H	I	J	K	L
1	USA		Los Angeles		L3		Main List		$I$3:$I$5	$J$3:$J$3	$K$3:$K$5	$L$3:$L$6
2							USA		SubList USA	Sublist FRANCE	SubList Paris	SubList Los Angeles
3							FRANCE		Los Angeles	Paris	P1	L1
4									Chicago		P2	L2
5									Pittsburgh		P3	L3
6												L4
7
Sheet1

A1, C1, and E1 are data validated cells with each a list.

A1 houses as MainList as Source:

=MainLlist

MainList is defined as:

=OFFSET(Sheet1!$G$2,0,0,MATCH(REPT("z",255),Sheet1!$G:$G)-MIN(ROW(Sheet1!$G$2))+1)

C1 houses as Source:

=INDIRECT(INDIRECT(A1))

E1 houses as Source:

=INDIRECT(INDIRECT(SUBSTITUTE(C1," ","")))

Sublist USA, an item in MainList, is defined as:

=Sheet1!$I$1

I1 on Sheet1 houses the formula:

=CELL("address",I3)&":"&CELL("address",INDEX(I:I,MATCH(REPT("z",255),I:I)))

Sublist FRANCE, also an item in MainList, is defined as:

=Sheet1!$J$1

J1 on Sheet1 houses the formula:

=CELL("address",J3)&":"&CELL("address",INDEX(J:J,MATCH(REPT("z",255),J:J)))

etc.

Sublist Los Angeles, which can be chosen from C1, is defined as:

=Sheet1!$L$1


Note that LosAngeles is used in the Names in Workbook, not Los Angeles. The latter form is understood by the data validation formula in E1 as LosAngeles.

L1 on Sheet1 houses the formula:

=CELL("address",L3)&":"&CELL("address",INDEX(L:L,MATCH(REPT("z",255),L:L)))

etc.

 

[Stormseed]

Oh Boy...whoa !!

I will need time to first figure out and understand this implementation and functionality of Dynamic Ranges.

I would try to concentrate and would revert incase I am stuck somewhere.

Thanks, Mr. Aladin. I really appreciate your efforts to make a fellowmate understand