| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Validation - Restriction
Hi
I want to use data validation and have been looking at the website www.contextures.com From here I have used the following formula in my data validation / source to link to a list and update automatically as new entires to my list are added / deleted =OFFSET(RegionStart,1,0,COUNTA(RegionColumn)-1,1) The problem is it allows me to enter values not in the list (there are no blank spaces when I select the list) and I would like to restrict this so that only values on the list can be restricted Thanks for your help -- Kevin |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Data Validation - Restriction
Kevin,
In my testing (XL2003) I can only enter data from the list when I select LIST as a DV option; anything else produces an error. "Kevin" wrote: Hi I want to use data validation and have been looking at the website www.contextures.com From here I have used the following formula in my data validation / source to link to a list and update automatically as new entires to my list are added / deleted =OFFSET(RegionStart,1,0,COUNTA(RegionColumn)-1,1) The problem is it allows me to enter values not in the list (there are no blank spaces when I select the list) and I would like to restrict this so that only values on the list can be restricted Thanks for your help -- Kevin |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Data Validation - Restriction
Hi - so I am selecting Allow = "list" also and then Data is greyed out and
then source "=OFFSET(RegionStart,1,0,COUNTA(RegionColumn)-1,1)" The list works fine but I can also enter any text i like but I want to restrict to the list any ideas? -- Kevin "Toppers" wrote: Kevin, In my testing (XL2003) I can only enter data from the list when I select LIST as a DV option; anything else produces an error. "Kevin" wrote: Hi I want to use data validation and have been looking at the website www.contextures.com From here I have used the following formula in my data validation / source to link to a list and update automatically as new entires to my list are added / deleted =OFFSET(RegionStart,1,0,COUNTA(RegionColumn)-1,1) The problem is it allows me to enter values not in the list (there are no blank spaces when I select the list) and I would like to restrict this so that only values on the list can be restricted Thanks for your help -- Kevin |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Data Validation - Restriction
Check a DV setting:
Data|Validation Switch to the "Error Alert" tab Is the "Show error alert..." box UNCHECKED" If yes, the cell wil allow anything you type. Does that help? *********** Regards, Ron XL2002, WinXP-Pro "Kevin" wrote: Hi - so I am selecting Allow = "list" also and then Data is greyed out and then source "=OFFSET(RegionStart,1,0,COUNTA(RegionColumn)-1,1)" The list works fine but I can also enter any text i like but I want to restrict to the list any ideas? -- Kevin "Toppers" wrote: Kevin, In my testing (XL2003) I can only enter data from the list when I select LIST as a DV option; anything else produces an error. "Kevin" wrote: Hi I want to use data validation and have been looking at the website www.contextures.com From here I have used the following formula in my data validation / source to link to a list and update automatically as new entires to my list are added / deleted =OFFSET(RegionStart,1,0,COUNTA(RegionColumn)-1,1) The problem is it allows me to enter values not in the list (there are no blank spaces when I select the list) and I would like to restrict this so that only values on the list can be restricted Thanks for your help -- Kevin |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Data Validation - Restriction
Sorry .. none!
This was my DV: "=OFFSET($A$1,1,0,COUNTA(A:A),1)" Do you want to post sample file to me and I'll look at it? ) "Kevin" wrote: Hi - so I am selecting Allow = "list" also and then Data is greyed out and then source "=OFFSET(RegionStart,1,0,COUNTA(RegionColumn)-1,1)" The list works fine but I can also enter any text i like but I want to restrict to the list any ideas? -- Kevin "Toppers" wrote: Kevin, In my testing (XL2003) I can only enter data from the list when I select LIST as a DV option; anything else produces an error. "Kevin" wrote: Hi I want to use data validation and have been looking at the website www.contextures.com From here I have used the following formula in my data validation / source to link to a list and update automatically as new entires to my list are added / deleted =OFFSET(RegionStart,1,0,COUNTA(RegionColumn)-1,1) The problem is it allows me to enter values not in the list (there are no blank spaces when I select the list) and I would like to restrict this so that only values on the list can be restricted Thanks for your help -- Kevin |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Data Validation - Restriction
Ron,
So obvious - flag is automatically set (in XL2003) so assumed this to be the state! "Ron Coderre" wrote: Check a DV setting: Data|Validation Switch to the "Error Alert" tab Is the "Show error alert..." box UNCHECKED" If yes, the cell wil allow anything you type. Does that help? *********** Regards, Ron XL2002, WinXP-Pro "Kevin" wrote: Hi - so I am selecting Allow = "list" also and then Data is greyed out and then source "=OFFSET(RegionStart,1,0,COUNTA(RegionColumn)-1,1)" The list works fine but I can also enter any text i like but I want to restrict to the list any ideas? -- Kevin "Toppers" wrote: Kevin, In my testing (XL2003) I can only enter data from the list when I select LIST as a DV option; anything else produces an error. "Kevin" wrote: Hi I want to use data validation and have been looking at the website www.contextures.com From here I have used the following formula in my data validation / source to link to a list and update automatically as new entires to my list are added / deleted =OFFSET(RegionStart,1,0,COUNTA(RegionColumn)-1,1) The problem is it allows me to enter values not in the list (there are no blank spaces when I select the list) and I would like to restrict this so that only values on the list can be restricted Thanks for your help -- Kevin |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Data Validation - Restriction
hi - error alert did not help
I tired using your formula below and it works exactly how I would like it - therefore the issues appears to be because I am using a reference (eg "RegionStart" instead of "$A1$") and/or the reference is contained on a different sheet within the workbook any thoughts -- Kevin "Toppers" wrote: Sorry .. none! This was my DV: "=OFFSET($A$1,1,0,COUNTA(A:A),1)" Do you want to post sample file to me and I'll look at it? ) "Kevin" wrote: Hi - so I am selecting Allow = "list" also and then Data is greyed out and then source "=OFFSET(RegionStart,1,0,COUNTA(RegionColumn)-1,1)" The list works fine but I can also enter any text i like but I want to restrict to the list any ideas? -- Kevin "Toppers" wrote: Kevin, In my testing (XL2003) I can only enter data from the list when I select LIST as a DV option; anything else produces an error. "Kevin" wrote: Hi I want to use data validation and have been looking at the website www.contextures.com From here I have used the following formula in my data validation / source to link to a list and update automatically as new entires to my list are added / deleted =OFFSET(RegionStart,1,0,COUNTA(RegionColumn)-1,1) The problem is it allows me to enter values not in the list (there are no blank spaces when I select the list) and I would like to restrict this so that only values on the list can be restricted Thanks for your help -- Kevin |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Data Validation - Restriction
Kevin,
My testing produced the same results as yours i.e. works if $A$1 but not with the named variable. What is RegionColumn? "Kevin" wrote: hi - error alert did not help I tired using your formula below and it works exactly how I would like it - therefore the issues appears to be because I am using a reference (eg "RegionStart" instead of "$A1$") and/or the reference is contained on a different sheet within the workbook any thoughts -- Kevin "Toppers" wrote: Sorry .. none! This was my DV: "=OFFSET($A$1,1,0,COUNTA(A:A),1)" Do you want to post sample file to me and I'll look at it? ) "Kevin" wrote: Hi - so I am selecting Allow = "list" also and then Data is greyed out and then source "=OFFSET(RegionStart,1,0,COUNTA(RegionColumn)-1,1)" The list works fine but I can also enter any text i like but I want to restrict to the list any ideas? -- Kevin "Toppers" wrote: Kevin, In my testing (XL2003) I can only enter data from the list when I select LIST as a DV option; anything else produces an error. "Kevin" wrote: Hi I want to use data validation and have been looking at the website www.contextures.com From here I have used the following formula in my data validation / source to link to a list and update automatically as new entires to my list are added / deleted =OFFSET(RegionStart,1,0,COUNTA(RegionColumn)-1,1) The problem is it allows me to enter values not in the list (there are no blank spaces when I select the list) and I would like to restrict this so that only values on the list can be restricted Thanks for your help -- Kevin |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| From several workbooks onto one excel worksheet | Excel Discussion (Misc queries) | |||
| data validation lists | Excel Discussion (Misc queries) | |||
| named range, data validation: list non-selected items, and new added items | Excel Discussion (Misc queries) | |||
| Automatic Data Validation drop down creation | Excel Discussion (Misc queries) | |||
| Pulling data from 1 sheet to another | Excel Worksheet Functions | |||
Linear Mode
