Nested Data Validation in Excel 1

[factotum]

Okay, I've got a problem that's driving me crazy. I have an Excel spreadsheet that will be used by people to summarize data. In one column, there are only a handful of choices that could be entered. Therefore, I set up a Data Validation for that column. I created a sheet called Validation, and on it created the list of possible choices. As per the "Help" documents, I defined that range as a name. Now, the heart of the matter is this: in the next column over, the choices available again are limited, but contingent on the previous choices available. In simple terms I need this:

If value of cell = "Acount" use data validation with source name "=PracticeAccount" or if cell = "Access" use data validation with source name "=PracticeAccess"... and so on until all the first list choices are cycled through. Blanks are okay. I have it checked in the Data Validation pane.

Other issues that are driving me mad with this problem:

1. When I define the name in the Insert>Name>Define dialog, I set the range of cells that are allowed. It works for a little bit, and then Excel changes the values to some asinine value... same column, but like 65541 rows down.
2. I'm afraid to admit my ignorance, but what does the $-sign mean? The "Help" in Excel throws me for a complete loop. I know it's either the keep the cell value the same, or to move it to the next row (i.e. relative cell position), but I can't remember which. I ask because in the Source of the Data Validation, sometimes it's "$G$3, and sometimes it's $G3... both seem to change values however on down the line.

I apologize for the long post, but I hope the detail will help any of you masters give me the solution.

Thanks a bunch in advance,

=Daniel

 

[DaleWatson123321]

Daniel,

The "easy" answer here, is regarding the use of the "$" character. It means keep the column or row "absolute".

If used for both the column and row of the cell address (e.g. =$A$1) this will keep the formula "absolute" no matter where the formula is copied. Another way of keeping a cell or range reference "absolute" (when copying), is to assign a range name and use that name in your formula.

Naturally, if you use only ONE $ character, only the column or row will remain absolute. e.g. with =A$1 copied down and across to other columns, the column letter will change, but the row number will remain the same (absolute).

For the "rest" of your "Data Validation" situation, I would strongly recommend that you offer to email your file to would-be contributors. This can potentially save MUCH time and effort - at BOTH ends. If you have any sensitive data, replace it with fictitious data that still reflects the type of data you're working with.

You can email me the file if you like, but I would also encourage other would-be contributors to offer to help if you are so inclined.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[xlbo]

Hi Daniel - I just had to do the very same thing for a little project that's just been completed

In data validation on your 2nd column, choose List and enter the following formula (amending references to range names:
=IF($A2="Acount",practiceAccount,IF($A2="Access",PracticeAccess,"Select A Value&quot)
where PracticeAccount and PracticeAccess are range names set up with a list of options in them
Obviously, you can continue on with further IFs

Dale has helped you with the meaning of $ and it should solve 1 & 2 - your range names need to be defined with "Doubledollars" ie summat like $A$1:$Z$100 otherwise they'll move and you'll get the effect that you are currently experiencing

HTH
~Geoff~

 

[factotum]

Thanks so much for the help. I'm moving in the right direction. However, when I add your IF statement into the source area of the Data Validation pane, I get the warning... The List Source must be a delimited list or a reference to a single column or row.

In addition, there's a limit to how much you can enter into the Data Validaiton Source area. I think I can solve this by using shorter variable names (I was just a few characters short), but it was another unexpected "ding."

Thanks again,
=Daniel

 

[xlbo]

The formula MUST be correct in its syntax - I got this error a coupla times. Also, your range names MUST be predefined before you put the formula in (and spelt correctly in the formula)
This is the EXACT formula that is in my "Source Area" box

=IF($N$23="Weather",eWType,IF($N$23="Sport",eSpType,IF($N$23="PPC",ePPCType,IF($N$23="Other",eOthType,"Select An Event Type&quot)))

where eWType etc are all predefined range names that contain lists of values. It DOES work, it's just a bit fiddly. If you are still struggling with this, and your data isn't too sensitive, feel free to send me a copy of the spreadsheet and I'll have a look (excel 97 or earlier tho - I ain't got 2000)
HTH
~Geoff~

 

[factotum]

Hey Geoff, I'm so close it's wonderful. I have only one issue left to resolve. I'm selecting a lot of rows in a particular column and setting the Data Validation. But I'm using an absolute cell assingment (i.e. $L$2). If I use $L2, then the numbers go all whacky on me, and I can't... eh, I just think I solved it. I don't know why it did that the first time, but now it's not. Success!

Thanks for all your help!

=Daniel