Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Data validation
--I am trying to understand what I am doing wrong. I selected the entire column C in worksheet 1 for my validation entry test. The Data validation references the list of valid account numbers shown below, which is a range contained in worksheet2: 10400 12200 21010 26716 29000 29037 The validation is set to reference account numbers from the above list (the range is set up as the entire column B where this list occurs, though the list is only 40 or so account numbers, the entire column is referenced in case I want to add additional account numbers to the approved list at a later date). The Data Validation tab is activated to stop and show the error alert after an invalid entry. (I want to limit the user to selecting only those accounts on the validation list). My problem is when I test it, it seems to accept any account number I enter in Cell C1 in worksheet1, without regard to the actual accounts I have set up on my data validation list in Column B in worksheet2. There is no error message that comes up when I enter the invalid account number, just a drop down box that allows me to enter any number I wish. What am I doing wrong???Any help greatly appreciated! DakotaSteve |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Data validation
dakotasteve Wrote: --I am trying to understand what I am doing wrong. I selected the entire column C in worksheet 1 for my validation entry test. The Data validation references the list of valid account numbers shown below, which is a range contained in worksheet2: 10400 12200 21010 26716 29000 29037 The validation is set to reference account numbers from the above list (the range is set up as the entire column B where this list occurs, though the list is only 40 or so account numbers, the entire column is referenced in case I want to add additional account numbers to the approved list at a later date). The Data Validation tab is activated to stop and show the error alert after an invalid entry. (I want to limit the user to selecting only those accounts on the validation list). My problem is when I test it, it seems to accept any account number I enter in Cell C1 in worksheet1, without regard to the actual accounts I have set up on my data validation list in Column B in worksheet2. There is no error message that comes up when I enter the invalid account number, just a drop down box that allows me to enter any number I wish. What am I doing wrong???Any help greatly appreciated! DakotaSteve Hi Dakotasteve, The problem is is that you have put the whole column B as data validation so you have 65,000+ blank cells in your validation. Limit it to your 40 account numbers only oldchippy :) -- oldchippy ------------------------------------------------------------------------ oldchippy's Profile: http://www.excelforum.com/member.php...o&userid=19907 View this thread: http://www.excelforum.com/showthread...hreadid=568072 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Data validation
Thanks OldChippy for your advice. However, is there a way I can make the
range accomdate growth in the list without having to manually update the range every time I do it? thx -- DakotaSteve "oldchippy" wrote: dakotasteve Wrote: --I am trying to understand what I am doing wrong. I selected the entire column C in worksheet 1 for my validation entry test. The Data validation references the list of valid account numbers shown below, which is a range contained in worksheet2: 10400 12200 21010 26716 29000 29037 The validation is set to reference account numbers from the above list (the range is set up as the entire column B where this list occurs, though the list is only 40 or so account numbers, the entire column is referenced in case I want to add additional account numbers to the approved list at a later date). The Data Validation tab is activated to stop and show the error alert after an invalid entry. (I want to limit the user to selecting only those accounts on the validation list). My problem is when I test it, it seems to accept any account number I enter in Cell C1 in worksheet1, without regard to the actual accounts I have set up on my data validation list in Column B in worksheet2. There is no error message that comes up when I enter the invalid account number, just a drop down box that allows me to enter any number I wish. What am I doing wrong???Any help greatly appreciated! DakotaSteve Hi Dakotasteve, The problem is is that you have put the whole column B as data validation so you have 65,000+ blank cells in your validation. Limit it to your 40 account numbers only oldchippy :) -- oldchippy ------------------------------------------------------------------------ oldchippy's Profile: http://www.excelforum.com/member.php...o&userid=19907 View this thread: http://www.excelforum.com/showthread...hreadid=568072 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Data validation
You cannot have the input cell as part of the list.
If you want to use all of C as a list, put your DV input cell in D1 or any other column. OR you could have the input cell as C1 and the list as C2:C65536 Gord Dibben MS Excel MVP On Thu, 3 Aug 2006 11:56:02 -0700, dakotasteve wrote: --I am trying to understand what I am doing wrong. I selected the entire column C in worksheet 1 for my validation entry test. The Data validation references the list of valid account numbers shown below, which is a range contained in worksheet2: 10400 12200 21010 26716 29000 29037 The validation is set to reference account numbers from the above list (the range is set up as the entire column B where this list occurs, though the list is only 40 or so account numbers, the entire column is referenced in case I want to add additional account numbers to the approved list at a later date). The Data Validation tab is activated to stop and show the error alert after an invalid entry. (I want to limit the user to selecting only those accounts on the validation list). My problem is when I test it, it seems to accept any account number I enter in Cell C1 in worksheet1, without regard to the actual accounts I have set up on my data validation list in Column B in worksheet2. There is no error message that comes up when I enter the invalid account number, just a drop down box that allows me to enter any number I wish. What am I doing wrong???Any help greatly appreciated! DakotaSteve |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Data validation
Old Chippy, I revised the range for the list to be C1:C40 in worksheet2, and
I still get no error message upon entering an invalid account # in worksheet1 using data validation? So the selection of the entire column C as the range for the list may not be the source of the problem??? -- DakotaSteve "oldchippy" wrote: dakotasteve Wrote: --I am trying to understand what I am doing wrong. I selected the entire column C in worksheet 1 for my validation entry test. The Data validation references the list of valid account numbers shown below, which is a range contained in worksheet2: 10400 12200 21010 26716 29000 29037 The validation is set to reference account numbers from the above list (the range is set up as the entire column B where this list occurs, though the list is only 40 or so account numbers, the entire column is referenced in case I want to add additional account numbers to the approved list at a later date). The Data Validation tab is activated to stop and show the error alert after an invalid entry. (I want to limit the user to selecting only those accounts on the validation list). My problem is when I test it, it seems to accept any account number I enter in Cell C1 in worksheet1, without regard to the actual accounts I have set up on my data validation list in Column B in worksheet2. There is no error message that comes up when I enter the invalid account number, just a drop down box that allows me to enter any number I wish. What am I doing wrong???Any help greatly appreciated! DakotaSteve Hi Dakotasteve, The problem is is that you have put the whole column B as data validation so you have 65,000+ blank cells in your validation. Limit it to your 40 account numbers only oldchippy :) -- oldchippy ------------------------------------------------------------------------ oldchippy's Profile: http://www.excelforum.com/member.php...o&userid=19907 View this thread: http://www.excelforum.com/showthread...hreadid=568072 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Data validation
dakotasteve Wrote: Thanks OldChippy for your advice. However, is there a way I can make the range accomdate growth in the list without having to manually update the range every time I do it? thx -- DakotaSteve "oldchippy" wrote: dakotasteve Wrote: --I am trying to understand what I am doing wrong. I selected the entire column C in worksheet 1 for my validation entry test. The Data validation references the list of valid account numbers shown below, which is a range contained in worksheet2: 10400 12200 21010 26716 29000 29037 The validation is set to reference account numbers from the above list (the range is set up as the entire column B where this list occurs, though the list is only 40 or so account numbers, the entire column is referenced in case I want to add additional account numbers to the approved list at a later date). The Data Validation tab is activated to stop and show the error alert after an invalid entry. (I want to limit the user to selecting only those accounts on the validation list). My problem is when I test it, it seems to accept any account number I enter in Cell C1 in worksheet1, without regard to the actual accounts I have set up on my data validation list in Column B in worksheet2. There is no error message that comes up when I enter the invalid account number, just a drop down box that allows me to enter any number I wish. What am I doing wrong???Any help greatly appreciated! DakotaSteve Hi Dakotasteve, The problem is is that you have put the whole column B as data validation so you have 65,000+ blank cells in your validation. Limit it to your 40 account numbers only oldchippy :) -- oldchippy ------------------------------------------------------------------------ oldchippy's Profile: http://www.excelforum.com/member.php...o&userid=19907 View this thread: http://www.excelforum.com/showthread...hreadid=568072 Take a look at this link to create a dynamic range http://www.ozgrid.com/Excel/DynamicRanges.htm oldchippy :) -- oldchippy ------------------------------------------------------------------------ oldchippy's Profile: http://www.excelforum.com/member.php...o&userid=19907 View this thread: http://www.excelforum.com/showthread...hreadid=568072 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Data validation
Sorry for this.
Misread the original post and thought column C was list source. What you need is a Dynamic Range on Sheet2 so's you can allow for increased size. See Debra Dalgleish's site for this. http://www.contextures.on.ca/xlNames01.html#Dynamic Gord On Thu, 03 Aug 2006 12:47:57 -0700, Gord Dibben <gorddibbATshawDOTca wrote: You cannot have the input cell as part of the list. If you want to use all of C as a list, put your DV input cell in D1 or any other column. OR you could have the input cell as C1 and the list as C2:C65536 Gord Dibben MS Excel MVP On Thu, 3 Aug 2006 11:56:02 -0700, dakotasteve wrote: --I am trying to understand what I am doing wrong. I selected the entire column C in worksheet 1 for my validation entry test. The Data validation references the list of valid account numbers shown below, which is a range contained in worksheet2: 10400 12200 21010 26716 29000 29037 The validation is set to reference account numbers from the above list (the range is set up as the entire column B where this list occurs, though the list is only 40 or so account numbers, the entire column is referenced in case I want to add additional account numbers to the approved list at a later date). The Data Validation tab is activated to stop and show the error alert after an invalid entry. (I want to limit the user to selecting only those accounts on the validation list). My problem is when I test it, it seems to accept any account number I enter in Cell C1 in worksheet1, without regard to the actual accounts I have set up on my data validation list in Column B in worksheet2. There is no error message that comes up when I enter the invalid account number, just a drop down box that allows me to enter any number I wish. What am I doing wrong???Any help greatly appreciated! DakotaSteve Gord Dibben MS Excel MVP |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Data validation
Hi There
Wow, thanks for the tip on the dynamic range. I will have to try that. But for now, for simplicity's sake, I tested this by reducing the list range in column B to just 40 rows, b2 through b39 in worksheet 2 (this range is named TB_Account). In the validation entry worksheet1 which selects all of column C in worksheet 1 for data validation with a reference to the list contained in the name range "TB_Account" in worksheet2, when I test I still get no error message when I enter a "non-list" account number into the cell which should require validation. Very puzzling! Anyone know why? -- DakotaSteve "Gord Dibben" wrote: Sorry for this. Misread the original post and thought column C was list source. What you need is a Dynamic Range on Sheet2 so's you can allow for increased size. See Debra Dalgleish's site for this. http://www.contextures.on.ca/xlNames01.html#Dynamic Gord On Thu, 03 Aug 2006 12:47:57 -0700, Gord Dibben <gorddibbATshawDOTca wrote: You cannot have the input cell as part of the list. If you want to use all of C as a list, put your DV input cell in D1 or any other column. OR you could have the input cell as C1 and the list as C2:C65536 Gord Dibben MS Excel MVP On Thu, 3 Aug 2006 11:56:02 -0700, dakotasteve wrote: --I am trying to understand what I am doing wrong. I selected the entire column C in worksheet 1 for my validation entry test. The Data validation references the list of valid account numbers shown below, which is a range contained in worksheet2: 10400 12200 21010 26716 29000 29037 The validation is set to reference account numbers from the above list (the range is set up as the entire column B where this list occurs, though the list is only 40 or so account numbers, the entire column is referenced in case I want to add additional account numbers to the approved list at a later date). The Data Validation tab is activated to stop and show the error alert after an invalid entry. (I want to limit the user to selecting only those accounts on the validation list). My problem is when I test it, it seems to accept any account number I enter in Cell C1 in worksheet1, without regard to the actual accounts I have set up on my data validation list in Column B in worksheet2. There is no error message that comes up when I enter the invalid account number, just a drop down box that allows me to enter any number I wish. What am I doing wrong???Any help greatly appreciated! DakotaSteve Gord Dibben MS Excel MVP |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Data validation
I cannot replicate your problem unless I uncheck the "Show error alert etc."
Gord On Thu, 3 Aug 2006 16:52:02 -0700, dakotasteve wrote: Hi There Wow, thanks for the tip on the dynamic range. I will have to try that. But for now, for simplicity's sake, I tested this by reducing the list range in column B to just 40 rows, b2 through b39 in worksheet 2 (this range is named TB_Account). In the validation entry worksheet1 which selects all of column C in worksheet 1 for data validation with a reference to the list contained in the name range "TB_Account" in worksheet2, when I test I still get no error message when I enter a "non-list" account number into the cell which should require validation. Very puzzling! Anyone know why? |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Data validation
Thanks Gordon and Chippy. My "show error alert" is checked and I still get
no error message when I enter in an account code that is not from the list, so unless I get more advice I will have to assume I have an unexplained anomaly in my Excel program. Steve -- DakotaSteve "Gord Dibben" wrote: I cannot replicate your problem unless I uncheck the "Show error alert etc." Gord On Thu, 3 Aug 2006 16:52:02 -0700, dakotasteve wrote: Hi There Wow, thanks for the tip on the dynamic range. I will have to try that. But for now, for simplicity's sake, I tested this by reducing the list range in column B to just 40 rows, b2 through b39 in worksheet 2 (this range is named TB_Account). In the validation entry worksheet1 which selects all of column C in worksheet 1 for data validation with a reference to the list contained in the name range "TB_Account" in worksheet2, when I test I still get no error message when I enter a "non-list" account number into the cell which should require validation. Very puzzling! Anyone know why? |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Data validation
Hey DS,
I read the variouos posts here, I am a bit of a rookie too. I have some code that I got from the contextures sample code site. See if this helps you get what you are after. Open a work book, have two sheets, one called "Lists" (your data) and one called "NameList" (your input sheet) On the Lists sheet, create your range of data in column A , then highlight all of range you want to use, I used 500 cells. At the top of the window across from fx there is a name window, it shows what cell you are in. Type the name NamedList in the window and hit enter, this will name your range of data. click o.k. and you should be on your way. You may have to experiment a little with it. Good luck with it. larry Now go up to the Insert button and click Insert, then name, then Define. There you will see your newly created nameList, select it and in the "refers to" window paste the following: =OFFSET(Lists!$A$1,0,0,COUNTA(Lists!$A:$A),1) click O.K. Now open your NameList sheet ( Actually, you can rename it to what ever you want). Highlight all the cells in a range in what ever column you want to use (again, I used 500 cells) then go up to Data, then select Validation and a window will open, in the validation criteria drop down select List. At the botttom a new window will open for the source, select the settings tab; in this type =NameList (the same as the defined list name you created). now select Input message tab, you can create a custom message for your users here. check the "Show input message when cell is selcted if you want to use this feature. now go to the Error Alert tab Check the box at the top th Show an eror after invalid data is entered. You can also choose to enter a message to the user here to direct them to enter only the appropriate data. |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Data validation
Thanks Larry
I am using Excel 2002, I think what I have discovered is that the named list range has to be on the same worksheet where the validation is occuring in order for the validation function to work. I don't know if Excel 2003 fixed this or not! thx all -- DakotaSteve "Larry" wrote: Hey DS, I read the variouos posts here, I am a bit of a rookie too. I have some code that I got from the contextures sample code site. See if this helps you get what you are after. Open a work book, have two sheets, one called "Lists" (your data) and one called "NameList" (your input sheet) On the Lists sheet, create your range of data in column A , then highlight all of range you want to use, I used 500 cells. At the top of the window across from fx there is a name window, it shows what cell you are in. Type the name NamedList in the window and hit enter, this will name your range of data. click o.k. and you should be on your way. You may have to experiment a little with it. Good luck with it. larry Now go up to the Insert button and click Insert, then name, then Define. There you will see your newly created nameList, select it and in the "refers to" window paste the following: =OFFSET(Lists!$A$1,0,0,COUNTA(Lists!$A:$A),1) click O.K. Now open your NameList sheet ( Actually, you can rename it to what ever you want). Highlight all the cells in a range in what ever column you want to use (again, I used 500 cells) then go up to Data, then select Validation and a window will open, in the validation criteria drop down select List. At the botttom a new window will open for the source, select the settings tab; in this type =NameList (the same as the defined list name you created). now select Input message tab, you can create a custom message for your users here. check the "Show input message when cell is selcted if you want to use this feature. now go to the Error Alert tab Check the box at the top th Show an eror after invalid data is entered. You can also choose to enter a message to the user here to direct them to enter only the appropriate data. |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Data validation
Steve
If you name the list range on the other sheet or workbook(if open) then it can be used in DV. In the source dialog you would enter =MyList where MyList is a named range. This goes all the way back to Excel 97 and has not changed so no fix required. Gord Dibben MS Excel MVP On Fri, 4 Aug 2006 15:41:02 -0700, dakotasteve wrote: Thanks Larry I am using Excel 2002, I think what I have discovered is that the named list range has to be on the same worksheet where the validation is occuring in order for the validation function to work. I don't know if Excel 2003 fixed this or not! thx all |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Data validation
Hi Gordon
YOur comments regarding the use of a named range on another worksheet are exactly what I assumed from reading my Excel texts, however, in my example, when I name the range in another worksheet and reference it, the validation process ignores it, but when I refer to "MyList" (the named range) and place it on the same worksheet in a column to the right of where my validation occurs, it works fine. Very strange. Steve -- DakotaSteve "Gord Dibben" wrote: Steve If you name the list range on the other sheet or workbook(if open) then it can be used in DV. In the source dialog you would enter =MyList where MyList is a named range. This goes all the way back to Excel 97 and has not changed so no fix required. Gord Dibben MS Excel MVP On Fri, 4 Aug 2006 15:41:02 -0700, dakotasteve wrote: Thanks Larry I am using Excel 2002, I think what I have discovered is that the named list range has to be on the same worksheet where the validation is occuring in order for the validation function to work. I don't know if Excel 2003 fixed this or not! thx all |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro question | Excel Worksheet Functions | |||
Inputting data to one worksheet for it effect another | Excel Discussion (Misc queries) | |||
existing data in case of data validation | Excel Discussion (Misc queries) | |||
named range, data validation: list non-selected items, and new added items | Excel Discussion (Misc queries) | |||
Pulling data from 1 sheet to another | Excel Worksheet Functions |