Data Validation based on cell value

[Ramnura]

Hi,

Can anyone help me on the Data validation based on the cell value.

Actual data table as follows;

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]325XX
[/TD]
[TD]Apple
[/TD]
[TD]Orange[/TD]
[TD]Mango[/TD]
[/TR]
[TR]
[TD]326XX
[/TD]
[TD]Black
[/TD]
[TD]White
[/TD]
[TD]Blue
[/TD]
[/TR]
[TR]
[TD]327XX
[/TD]
[TD]Car
[/TD]
[TD]Bike
[/TD]
[TD]Bus
[/TD]
[/TR]
[TR]
[TD]328XX
[/TD]
[TD]Laptop
[/TD]
[TD]Wifi
[/TD]
[TD]Mouse
[/TD]
[/TR]
[TR]
[TD]329XX
[/TD]
[TD]India
[/TD]
[TD]America
[/TD]
[TD]England
[/TD]
[/TR]
</tbody>[/TABLE]









Data validation requirements: If cell value "325XX", Data validation list should have "Apple, Orange, Mango"
if Cell value "326XX" Data validation list should have "Black, White, Blue"

Conditions are:
1. Dynamic name (Name Manager) - Its not possible as cell values (Criteria) are many

I am very much struggling to set up a formula for the above requirements.

Requesting your great help on this.

Many thanks!
Karuvaayan

 

[Sektor]

Data Validation won't help you because it dynamically list to validate against. You need VBA code to do it.

 

[inactiveuserps07]

@Ramnura, you can have dynamic lists in data validation using the INDIRECT() function. Say, for instance, that the table in your original post above was in Sheet2!A1:D5. And say that your data validation list for Sheet1!A2 were a list drawn from Sheet2!$A$1:$A$5. Your data validation for Sheet2!B2 could then be a list with the following entered as "Source":

=INDIRECT("Sheet2!$B$"&MATCH($A$2,Sheet2!$A$1:$A$5,0)&":$D$"&MATCH($A$2,Sheet2!$A$1:$A$5,0))

 

[Ramnura]

@ErikTyler: Many thanks for your great help which can save my time and Energy.

Thanks a lot once again

 

[inactiveuserps07]

Glad to know it was useful, @Ramnura.

 

[Ramnura]

@ErikTyler: Sorry to getting you back!The above formula does not work when we go for other work book.

even there is no error on the selection and criteria

Requesting your help to solve the issue, thanks!

 

[Ramnura]

Please answer @ErikTyler

 

[inactiveuserps07]

@Ramnura, I have replied to both of your private messages.

Please do keep in mind that this is a volunteer board for minor formula and code help, and not a free service for complete solutions.