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.

 

[Jon von der Heyden]

Hi

I think a good place to start is to read this.

Regards
Jon

 

[Stormseed]

Hi

I think a good place to start is to read this.

Regards
Jon

Jon, thanks for the link. It really helps you know ! Neverthless I found some more help in regards to my question:

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

thanks again, mate

 

[Stormseed]

I gave it a shot creating the dependant lists with "Static Named Ranges" as in the link provided by Jon. It is a resolution provided by one of the board guru Mr. Aladin.

However, I am trying since last couple of hours and cant find a way to make it work. Everytime I validate the List in Column B with indirect(), it gives me the following error:

"The Source currently evaluates to an error. Do you wish to continue?"

I am so tired now...Any ideas where I am going wrong ?

 

[Jon von der Heyden]

Can you give us some idea about the lists and what formula you are using in validation? Maybe a small sample of your data?

 

[Stormseed]

Can you give us some idea about the lists and what formula you are using in validation? Maybe a small sample of your data?

thanks again Jon,

Here is the information table (deleted some rows as the list is long):

Call Logging Dispositions ver 1.01.xls
	DA	DB	DC	DD	DE
1	AO-AccountOpening&Modification	AO-Accountnotactivated	AP-Approvalrequired	BR-Brokeragecreditnotreceived	CB-Cheque/payorder/DDnotreceived/delayed
2	AP-ForApproval	AO-Activationofdeactivatedclientaccount		BR-Brokeragecreditwronglygiven	CB-Chequebounced
3	BR-Brokerage	AO-BankModificationRequest		BR-BrokerageRectifiction/ChangeofBrokerageSlab	CB-Chequedeposited,butcreditnotreflectinginclientledger
4	CB-ClientBanking	AO-CommodityA/cOpening		BR-IssueofTDScertificte	CB-chequestoptransfer
5	CP-Compliance	AO-Trading/DPDeactivationorclosurerequest		BR-Requestforchangeinclientmapping	CB-IntimationofChequeDeposit
Data

In the table:
the range in Column DA is named as "Departments",
the range in Column DB is named as "AO"
the range in column DC is named as "AP"
the range in column DD is named as "BR"
the range in column DE is named as "CB"

The range in DA which is the Primary list is shown in Column C of my worksheet. The first row is the header row. I need to have a list populated in Column D of my worksheet which is dependant on the selection I make in Column C. If I select AP - For Approval then only the range in column DC should be seen while I click the dropdown arrow.

I use the following formula in cell D2:

=INDIRECT(C2)

thanks for your kind help.

 

[Aladin Akyurek]

=INDIRECT(LEFT(C2,2))

 

[Stormseed]

INDIRECT(LEFT(C2,2))

Sir, that was a quickie and it does look so simple. However it did not click in my mind. thanks again...really !

Also it worked the other way, I changed the names of the "named ranges" from Col DB to DE to whole names which are reflected in Col DA.

Doing this it worked like a charm !!! I decide to go with your input of the nesting the Left() as it looks nice and tidy

 

[Stormseed]

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.

 

[GlennUK]

Stormseed,
why are you making a provision of extra blank cells in all of the Named Ranges exactly?