• 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.

Requesting help on data validation based on information from a different cell.

G

Glaucus

New Member
Hello all! I have only come upon this wondering website a few days ago and have decided to become a member of the forum since I have a problem with a project I am currently working on... Using Excel 2010.


The situation is as follows.


I am attempting to create a workbook where one column will have a pull down list with a number of options for the user to choose. Once the user chooses whatever they want from that column's list, the data that can be entered in another field is then dependent on what the user choose from the list. Here's an example...


A1 contains a list that includes the following...


Alpha

Beta

Gamma


What I am trying to achieve is to have a data validation based on what is chosen. Example...


If Alpha is chosen in A1, B1 can only have values 0-30

If Beta is chosen in A1, B1 can only have values 31-70

If Gamma is chosen in A1, B1 can only have values 71-100


I attempted to create a nested IF statement in B1 that looks like this just to get things started...


=IF(A1=0," ",

IF(A1="Alpha","Enter Alpha Points",

IF(A1="Beta","Enter Beta Points",

IF(A1="Gamma","Enter Gamma Points","ERROR"))))


Of course, upon finishing this I realized that using an IF statement to accomplish the data validating is a bit beyond what I know in Excel... And the options in the Data Validation tool in the ribbon menu were not intuitive to me.


Any and all help is most appreciated! If there is more info that is needed I will attempt to provide what I can.
 
If the list is more extensive, you might try here:

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


If it's just those three, this formula as a custom data validation for B1 will work:

=AND((A1="Alpha")*0+(A1="Beta")*31+(A1="Gamma")*71<=B1,(A1="Alpha")*30+(A1="Beta")*70+(A1="Gamma")*100>=B1)
 
Thank you for the response! Unfortunately, I could not correlate the data on the website provided with what I am trying to do, but everything else I could work with and it's doing just fine!


Thanks again!


If I have any more questions I'll be sure to check out this forum again.
 
You must log in or register to reply here.
Back
Top