• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Data Validation with Multiple References

S

Saturn24

New Member
I am building a product configuration spreadsheet in Excel 2010 with rules based dependencies. I am attempting to create a data validation formula that will reference a previous cell with six possible entries and then result in two possible answers.

First set of choices:
ZDrive_0.125
ZDrive_0.25
Fast_ZDrive_0.50

Desired result:
4090u 4" Handling Upgrade Kit


Second set of choices:
ThinWafer_ZDrive_0.125
ThinWafer_ZDrive_0.25
ThinWafer_Fast_ZDrive_0.50
Desired result:
4090u 4" ThinWafer Handling Upgrade Kit

Any assistance would be greatly appreciated.
 
Hi Saturn,

Welcome to the forum.

A sample file will help the reader to understand you problem quickly and fetch you a solution easily.
So kindly upload a sample file.

Regards,
 
Saturn24 said:
I am building a product configuration spreadsheet in Excel 2010 with rules based dependencies. I am attempting to create a data validation formula that will reference a previous cell with six possible entries and then result in two possible answers.

First set of choices:
ZDrive_0.125
ZDrive_0.25
Fast_ZDrive_0.50

Desired result:
4090u 4" Handling Upgrade Kit


Second set of choices:
ThinWafer_ZDrive_0.125
ThinWafer_ZDrive_0.25
ThinWafer_Fast_ZDrive_0.50
Desired result:
4090u 4" ThinWafer Handling Upgrade Kit

Any assistance would be greatly appreciated.
Click to expand...

Please have a look
http://chandoo.org/wp/2014/02/13/dynamic-cascading-dropdowns-that-reset/
 
Here is my sample file. Cell C10 is dependent on C2. I am attempting to get cell C22 to relate to the selections from C10. I am having difficulty as a result of the combination of choices as described in my first posting.
 

Attachments

  • 4090 Prober Configuration Form.xlsm
    41.6 KB · Views: 7
Here is what I expect in C22.....

If the result of C10 is:
ZDrive_0.125
ZDrive_0.25
Fast_ZDrive_0.50
Desired result:
4090u 4" Handling Upgrade Kit


If the result of C10 is:
ThinWafer_ZDrive_0.125
ThinWafer_ZDrive_0.25
ThinWafer_Fast_ZDrive_0.50
Desired result:
4090u 4" ThinWafer Handling Upgrade Kit

How would I incorporate the INDIRECT function with multiple selection choices?
 
@Saturn24

If you want single answer for multiple enteries of C10 then create a table and in C22 use VLOOKUP formula directly.
Why are you going for list data validation in C22?

Regards,
 
Hi ,

I am not sure I have understood you , but if you want either this or that i.e. you have only two choices , and if the user does not have to select any one of them , then a simple IF statement should do the job :

=IF(C10="","",IF(ISNUMBER(SEARCH("Thin",C10)),VLOOKUP!A60,VLOOKUP!A59))

Narayan
 
Somendra Misra said:
@Saturn24

If you want single answer for multiple enteries of C10 then create a table and in C22 use VLOOKUP formula directly.
Why are you going for list data validation in C22?

Regards,
Click to expand...


How do do I structure the VLOOKUP formula so the user will only be presented with the correct selection (one of two possible) based on the results from C10?
 
NARAYANK991 said:
Hi ,

I am not sure I have understood you , but if you want either this or that i.e. you have only two choices , and if the user does not have to select any one of them , then a simple IF statement should do the job :

=IF(C10="","",IF(ISNUMBER(SEARCH("Thin",C10)),VLOOKUP!A60,VLOOKUP!A59))

Narayan
Click to expand...

When I applied this formula through data validation, now the formula itself appears in the drop down list of C22.
 
Hi ,

The purpose of data validation is two fold :

1. Allow the user to select the data that is to be put into a worksheet cell

2. Ensure that the user cannot enter invalid data into the cell

When the number of options is just 2 , and if the data can be derived using a formula , using data validation does not serve any purpose ; the formula I posted is to be directly entered into C22 , and not to be used as data validation for that cell.

Narayan
 
NARAYANK991 said:
Hi ,

The purpose of data validation is two fold :

1. Allow the user to select the data that is to be put into a worksheet cell

2. Ensure that the user cannot enter invalid data into the cell

When the number of options is just 2 , and if the data can be derived using a formula , using data validation does not serve any purpose ; the formula I posted is to be directly entered into C22 , and not to be used as data validation for that cell.

Narayan
Click to expand...

Thank you so much for your response. The reason I had chosen to use data validation is the selection in C22 is not mandatory, so I need the dependent choice to be in a drop down so the user can select it is required for their application. By entering the formula directly in C22, it does select the correct type, but it makes it mandatory. Let me know if you have any suggestions.
 
Hi ,

OK , understood. However , a solution is somewhat difficult. Do you need this only in C22 or in other cells too ?

Narayan
 
Hi ,

In that case why not just put the formula in C22 , and in case the user wishes to remove the data which is displayed by means of the macro , they can clear the cell.

Narayan
 
NARAYANK991 said:
Hi ,

In that case why not just put the formula in C22 , and in case the user wishes to remove the data which is displayed by means of the macro , they can clear the cell.

Narayan
Click to expand...

The problem is we are dealing with users that are not that detail oriented.....if C22 gets populated automatically, I can be sure that a high percentage of the time, they will forget to remove the entry. The result will be that we will end up spending money on an option that is not required, and the customer will receive parts that are not required. I am trying to make this as foolproof as possible.
 
Hi ,

In that case , is it possible to introduce a checkbox or a radio button , which is unchecked by default ; the state of this checkbox / radio button can be used as an additional check within the above IF formula , so that till the user checks the checkbox / radio button , C22 will remain blank ; if the user clicks on the checkbox / radio button to select it , then the formula based option will be displayed in C22.

Narayan
 
NARAYANK991 said:
Hi ,

In that case , is it possible to introduce a checkbox or a radio button , which is unchecked by default ; the state of this checkbox / radio button can be used as an additional check within the above IF formula , so that till the user checks the checkbox / radio button , C22 will remain blank ; if the user clicks on the checkbox / radio button to select it , then the formula based option will be displayed in C22.

Narayan
Click to expand...

Can you describe in a bit more detail how to set that up?
 
You must log in or register to reply here.
Back
Top