Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Mind-numbing simple data validation ... string from 1 to 64 chars
Hi, all,
I've combed the discussion group as well as some of the great web resources for Excel programming & worksheet functions, and I've come to spend way too much time on what should be a simple data validation formula. First, I just wanted to prevent an empty cell in Column A. I noticed that data validation doesn't seem to kick in if you Tab or Enter over the cell -- DV only is working when the cursor's in the cell, by typing or clicking in cell or formular bar. I have tried the following custom validation rules without success, both with and without the $ before the A: =LEN($A)0 =NOT(ISBLANK($A) =NOT(EMPTY) They do work if I have the cursor in the cell, but again, Tabbing or pressing Enter bypasses Data Validation -- am I left with figuring out how to disable the Tab & Enter keys, or with reexaming the cell entires when the user leaves this routine? Yuck! Since I'm in it this far, I also want to have the length of the entry be from 1 to 64 characters (it's a text field, alphanumeric OK) -- I had this part working already, but wanted to add the "non-empty" requirement as well, and now I'm stuck. If I could also enforce unique entries in the entire column, that woudl be ideal. I found a formula for this but it's not working. I think it is overly stringent, preventing even substrings, too, which is not what I want. Here's the data validation formulas I was using for uniqueness: This entry goes in DV in cell A1: =ISERROR(MATCH(A1,A2:A50,0)) and this one in cells A2:A9000 =ISERROR(MATCH(A2,INDIRECT("$A$1:$A$"&ROW()-1),0)) Your help is greatly appreciated, especailly at this late hour, <grins Will Finkle San Diego |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Mind-numbing simple data validation ... string from 1 to 64 chars
Hi Will:
This is only a single cell example of how to enforce data entry once a cell has been selected. The example uses cell B9. Once B9 has been Selected by either the mouse or the arrow keys or the ENTER key or TAB key, data must be entered before another cell can be Selected. This macro goes in the worksheet code area: Private Sub Worksheet_SelectionChange(ByVal Target As Range) Set r = Range("B9") s = "You may not leave B9 empty" If Target.Count 1 Then Exit Sub End If If Not Intersect(Target, r) Is Nothing Then got_there = True Exit Sub End If If got_there Then If IsEmpty(r) Then MsgBox (s) Application.EnableEvents = False r.Select Application.EnableEvents = True Exit Sub End If got_there = False End If End Sub The uniqueness thing is much easier. Say we have a table from Z3 to Z17 and only want unique entries. Set data validation in Z3 to FormulaIs: =COUNTIF($Z$3:$Z$17,Z3)<2 and copy the validation down the table -- Gary''s Student - gsnu200744 "Will Finkle" wrote: Hi, all, I've combed the discussion group as well as some of the great web resources for Excel programming & worksheet functions, and I've come to spend way too much time on what should be a simple data validation formula. First, I just wanted to prevent an empty cell in Column A. I noticed that data validation doesn't seem to kick in if you Tab or Enter over the cell -- DV only is working when the cursor's in the cell, by typing or clicking in cell or formular bar. I have tried the following custom validation rules without success, both with and without the $ before the A: =LEN($A)0 =NOT(ISBLANK($A) =NOT(EMPTY) They do work if I have the cursor in the cell, but again, Tabbing or pressing Enter bypasses Data Validation -- am I left with figuring out how to disable the Tab & Enter keys, or with reexaming the cell entires when the user leaves this routine? Yuck! Since I'm in it this far, I also want to have the length of the entry be from 1 to 64 characters (it's a text field, alphanumeric OK) -- I had this part working already, but wanted to add the "non-empty" requirement as well, and now I'm stuck. If I could also enforce unique entries in the entire column, that woudl be ideal. I found a formula for this but it's not working. I think it is overly stringent, preventing even substrings, too, which is not what I want. Here's the data validation formulas I was using for uniqueness: This entry goes in DV in cell A1: =ISERROR(MATCH(A1,A2:A50,0)) and this one in cells A2:A9000 =ISERROR(MATCH(A2,INDIRECT("$A$1:$A$"&ROW()-1),0)) Your help is greatly appreciated, especailly at this late hour, <grins Will Finkle San Diego |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Mind-numbing simple data validation ... string from 1 to 64 ch
Thanks, Gary's Student, for the quick response and the terrific help.
I'm *almost* there. I've got the uniqueness code, no problem, thank you. But with the selection change sub, I have modified it thusly to activate the algorithm whenever the selection is a cell in the A column. But it's exiting the routing Here's the code I put in front, so it would only enter the routine for cells in the A column If InStr(1, ActiveCell.Address, "$A$") Then s = "You may not leave the Menu Item empty" Set r = ActiveCell End If But it's exiting out where it checks the intersection of Target & 'r', because got_there is true (so Exit Sub). If Not Intersect(Target, r) Is Nothing Then got_there = True Exit Sub End If So I backed up a step and just tried your code as written, on cell B9, and even though the code executed all the way through to the end, I was still able to tab to the next cell without the message box firing. Am I missing something? Thanks 1,000,000! --Will "Gary''s Student" wrote: Hi Will: This is only a single cell example of how to enforce data entry once a cell has been selected. The example uses cell B9. Once B9 has been Selected by either the mouse or the arrow keys or the ENTER key or TAB key, data must be entered before another cell can be Selected. This macro goes in the worksheet code area: Private Sub Worksheet_SelectionChange(ByVal Target As Range) Set r = Range("B9") s = "You may not leave B9 empty" If Target.Count 1 Then Exit Sub End If If Not Intersect(Target, r) Is Nothing Then got_there = True Exit Sub End If If got_there Then If IsEmpty(r) Then MsgBox (s) Application.EnableEvents = False r.Select Application.EnableEvents = True Exit Sub End If got_there = False End If End Sub The uniqueness thing is much easier. Say we have a table from Z3 to Z17 and only want unique entries. Set data validation in Z3 to FormulaIs: =COUNTIF($Z$3:$Z$17,Z3)<2 and copy the validation down the table -- Gary''s Student - gsnu200744 "Will Finkle" wrote: Hi, all, I've combed the discussion group as well as some of the great web resources for Excel programming & worksheet functions, and I've come to spend way too much time on what should be a simple data validation formula. First, I just wanted to prevent an empty cell in Column A. I noticed that data validation doesn't seem to kick in if you Tab or Enter over the cell -- DV only is working when the cursor's in the cell, by typing or clicking in cell or formular bar. I have tried the following custom validation rules without success, both with and without the $ before the A: =LEN($A)0 =NOT(ISBLANK($A) =NOT(EMPTY) They do work if I have the cursor in the cell, but again, Tabbing or pressing Enter bypasses Data Validation -- am I left with figuring out how to disable the Tab & Enter keys, or with reexaming the cell entires when the user leaves this routine? Yuck! Since I'm in it this far, I also want to have the length of the entry be from 1 to 64 characters (it's a text field, alphanumeric OK) -- I had this part working already, but wanted to add the "non-empty" requirement as well, and now I'm stuck. If I could also enforce unique entries in the entire column, that woudl be ideal. I found a formula for this but it's not working. I think it is overly stringent, preventing even substrings, too, which is not what I want. Here's the data validation formulas I was using for uniqueness: This entry goes in DV in cell A1: =ISERROR(MATCH(A1,A2:A50,0)) and this one in cells A2:A9000 =ISERROR(MATCH(A2,INDIRECT("$A$1:$A$"&ROW()-1),0)) Your help is greatly appreciated, especailly at this late hour, <grins Will Finkle San Diego |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Mind-numbing simple data validation ... string from 1 to 64 ch
Hi Will:
Check back tomorrow. The Help Center is really backed up now. -- Gary''s Student - gsnu200745 "Will Finkle" wrote: Thanks, Gary's Student, for the quick response and the terrific help. I'm *almost* there. I've got the uniqueness code, no problem, thank you. But with the selection change sub, I have modified it thusly to activate the algorithm whenever the selection is a cell in the A column. But it's exiting the routing Here's the code I put in front, so it would only enter the routine for cells in the A column If InStr(1, ActiveCell.Address, "$A$") Then s = "You may not leave the Menu Item empty" Set r = ActiveCell End If But it's exiting out where it checks the intersection of Target & 'r', because got_there is true (so Exit Sub). If Not Intersect(Target, r) Is Nothing Then got_there = True Exit Sub End If So I backed up a step and just tried your code as written, on cell B9, and even though the code executed all the way through to the end, I was still able to tab to the next cell without the message box firing. Am I missing something? Thanks 1,000,000! --Will "Gary''s Student" wrote: Hi Will: This is only a single cell example of how to enforce data entry once a cell has been selected. The example uses cell B9. Once B9 has been Selected by either the mouse or the arrow keys or the ENTER key or TAB key, data must be entered before another cell can be Selected. This macro goes in the worksheet code area: Private Sub Worksheet_SelectionChange(ByVal Target As Range) Set r = Range("B9") s = "You may not leave B9 empty" If Target.Count 1 Then Exit Sub End If If Not Intersect(Target, r) Is Nothing Then got_there = True Exit Sub End If If got_there Then If IsEmpty(r) Then MsgBox (s) Application.EnableEvents = False r.Select Application.EnableEvents = True Exit Sub End If got_there = False End If End Sub The uniqueness thing is much easier. Say we have a table from Z3 to Z17 and only want unique entries. Set data validation in Z3 to FormulaIs: =COUNTIF($Z$3:$Z$17,Z3)<2 and copy the validation down the table -- Gary''s Student - gsnu200744 "Will Finkle" wrote: Hi, all, I've combed the discussion group as well as some of the great web resources for Excel programming & worksheet functions, and I've come to spend way too much time on what should be a simple data validation formula. First, I just wanted to prevent an empty cell in Column A. I noticed that data validation doesn't seem to kick in if you Tab or Enter over the cell -- DV only is working when the cursor's in the cell, by typing or clicking in cell or formular bar. I have tried the following custom validation rules without success, both with and without the $ before the A: =LEN($A)0 =NOT(ISBLANK($A) =NOT(EMPTY) They do work if I have the cursor in the cell, but again, Tabbing or pressing Enter bypasses Data Validation -- am I left with figuring out how to disable the Tab & Enter keys, or with reexaming the cell entires when the user leaves this routine? Yuck! Since I'm in it this far, I also want to have the length of the entry be from 1 to 64 characters (it's a text field, alphanumeric OK) -- I had this part working already, but wanted to add the "non-empty" requirement as well, and now I'm stuck. If I could also enforce unique entries in the entire column, that woudl be ideal. I found a formula for this but it's not working. I think it is overly stringent, preventing even substrings, too, which is not what I want. Here's the data validation formulas I was using for uniqueness: This entry goes in DV in cell A1: =ISERROR(MATCH(A1,A2:A50,0)) and this one in cells A2:A9000 =ISERROR(MATCH(A2,INDIRECT("$A$1:$A$"&ROW()-1),0)) Your help is greatly appreciated, especailly at this late hour, <grins Will Finkle San Diego |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Mind-numbing simple data validation ... string from 1 to 64 ch
Thanks, Gary, will do -- I appreciate your letting me know! :-)
"Gary''s Student" wrote: Hi Will: Check back tomorrow. The Help Center is really backed up now. -- Gary''s Student - gsnu200745 "Will Finkle" wrote: Thanks, Gary's Student, for the quick response and the terrific help. I'm *almost* there. I've got the uniqueness code, no problem, thank you. But with the selection change sub, I have modified it thusly to activate the algorithm whenever the selection is a cell in the A column. But it's exiting the routing Here's the code I put in front, so it would only enter the routine for cells in the A column If InStr(1, ActiveCell.Address, "$A$") Then s = "You may not leave the Menu Item empty" Set r = ActiveCell End If But it's exiting out where it checks the intersection of Target & 'r', because got_there is true (so Exit Sub). If Not Intersect(Target, r) Is Nothing Then got_there = True Exit Sub End If So I backed up a step and just tried your code as written, on cell B9, and even though the code executed all the way through to the end, I was still able to tab to the next cell without the message box firing. Am I missing something? Thanks 1,000,000! --Will "Gary''s Student" wrote: Hi Will: This is only a single cell example of how to enforce data entry once a cell has been selected. The example uses cell B9. Once B9 has been Selected by either the mouse or the arrow keys or the ENTER key or TAB key, data must be entered before another cell can be Selected. This macro goes in the worksheet code area: Private Sub Worksheet_SelectionChange(ByVal Target As Range) Set r = Range("B9") s = "You may not leave B9 empty" If Target.Count 1 Then Exit Sub End If If Not Intersect(Target, r) Is Nothing Then got_there = True Exit Sub End If If got_there Then If IsEmpty(r) Then MsgBox (s) Application.EnableEvents = False r.Select Application.EnableEvents = True Exit Sub End If got_there = False End If End Sub The uniqueness thing is much easier. Say we have a table from Z3 to Z17 and only want unique entries. Set data validation in Z3 to FormulaIs: =COUNTIF($Z$3:$Z$17,Z3)<2 and copy the validation down the table -- Gary''s Student - gsnu200744 "Will Finkle" wrote: Hi, all, I've combed the discussion group as well as some of the great web resources for Excel programming & worksheet functions, and I've come to spend way too much time on what should be a simple data validation formula. First, I just wanted to prevent an empty cell in Column A. I noticed that data validation doesn't seem to kick in if you Tab or Enter over the cell -- DV only is working when the cursor's in the cell, by typing or clicking in cell or formular bar. I have tried the following custom validation rules without success, both with and without the $ before the A: =LEN($A)0 =NOT(ISBLANK($A) =NOT(EMPTY) They do work if I have the cursor in the cell, but again, Tabbing or pressing Enter bypasses Data Validation -- am I left with figuring out how to disable the Tab & Enter keys, or with reexaming the cell entires when the user leaves this routine? Yuck! Since I'm in it this far, I also want to have the length of the entry be from 1 to 64 characters (it's a text field, alphanumeric OK) -- I had this part working already, but wanted to add the "non-empty" requirement as well, and now I'm stuck. If I could also enforce unique entries in the entire column, that woudl be ideal. I found a formula for this but it's not working. I think it is overly stringent, preventing even substrings, too, which is not what I want. Here's the data validation formulas I was using for uniqueness: This entry goes in DV in cell A1: =ISERROR(MATCH(A1,A2:A50,0)) and this one in cells A2:A9000 =ISERROR(MATCH(A2,INDIRECT("$A$1:$A$"&ROW()-1),0)) Your help is greatly appreciated, especailly at this late hour, <grins Will Finkle San Diego |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Mind-numbing simple data validation ... string from 1 to 64 ch
Hi Will:
1. First delete the old macro. €śB9€ť is just too €śbenign€ť! 2. In a standard module, enter: Public where_was_I As Range Sub startup() Set where_was_I = ActiveCell Application.EnableEvents = True End Sub The Public, static, variable where_was_I €śremembers€ť where we were before changing Selection. Startup initializes this variable. 3. In the worksheet code area, enter: Private Sub Worksheet_SelectionChange(ByVal Target As Range) ' ' Version 2 - The Sequel ' Dim on_a As Boolean Dim was_on_a As Boolean Set ra = Range("A:A") s = "You may not leave a column A cell empty" was_on_a = True If Intersect(where_was_I, ra) Is Nothing Then was_on_a = False End If If Target.Address = where_was_I.Address Then Exit Sub If was_on_a Then If IsEmpty(where_was_I) Then MsgBox (s) Application.EnableEvents = False where_was_I.Select Application.EnableEvents = True Exit Sub Else Set where_was_I = Target End If Else Set where_was_I = Target End If End Sub 4. Before changing Selection, run the startup macro The logic is a little funky. Whenever we move to a new cell, we look back to where we came from (previous cell). If the previous cell was on column A and that previous cell is empty, the warning is issued and the user is kicked back to that previous cell. Just remember to delete the old version prior to installing the new one. Update this post to let us know how you are doing. -- Gary''s Student - gsnu200745 "Will Finkle" wrote: Thanks, Gary, will do -- I appreciate your letting me know! :-) "Gary''s Student" wrote: Hi Will: Check back tomorrow. The Help Center is really backed up now. -- Gary''s Student - gsnu200745 "Will Finkle" wrote: Thanks, Gary's Student, for the quick response and the terrific help. I'm *almost* there. I've got the uniqueness code, no problem, thank you. But with the selection change sub, I have modified it thusly to activate the algorithm whenever the selection is a cell in the A column. But it's exiting the routing Here's the code I put in front, so it would only enter the routine for cells in the A column If InStr(1, ActiveCell.Address, "$A$") Then s = "You may not leave the Menu Item empty" Set r = ActiveCell End If But it's exiting out where it checks the intersection of Target & 'r', because got_there is true (so Exit Sub). If Not Intersect(Target, r) Is Nothing Then got_there = True Exit Sub End If So I backed up a step and just tried your code as written, on cell B9, and even though the code executed all the way through to the end, I was still able to tab to the next cell without the message box firing. Am I missing something? Thanks 1,000,000! --Will "Gary''s Student" wrote: Hi Will: This is only a single cell example of how to enforce data entry once a cell has been selected. The example uses cell B9. Once B9 has been Selected by either the mouse or the arrow keys or the ENTER key or TAB key, data must be entered before another cell can be Selected. This macro goes in the worksheet code area: Private Sub Worksheet_SelectionChange(ByVal Target As Range) Set r = Range("B9") s = "You may not leave B9 empty" If Target.Count 1 Then Exit Sub End If If Not Intersect(Target, r) Is Nothing Then got_there = True Exit Sub End If If got_there Then If IsEmpty(r) Then MsgBox (s) Application.EnableEvents = False r.Select Application.EnableEvents = True Exit Sub End If got_there = False End If End Sub The uniqueness thing is much easier. Say we have a table from Z3 to Z17 and only want unique entries. Set data validation in Z3 to FormulaIs: =COUNTIF($Z$3:$Z$17,Z3)<2 and copy the validation down the table -- Gary''s Student - gsnu200744 "Will Finkle" wrote: Hi, all, I've combed the discussion group as well as some of the great web resources for Excel programming & worksheet functions, and I've come to spend way too much time on what should be a simple data validation formula. First, I just wanted to prevent an empty cell in Column A. I noticed that data validation doesn't seem to kick in if you Tab or Enter over the cell -- DV only is working when the cursor's in the cell, by typing or clicking in cell or formular bar. I have tried the following custom validation rules without success, both with and without the $ before the A: =LEN($A)0 =NOT(ISBLANK($A) =NOT(EMPTY) They do work if I have the cursor in the cell, but again, Tabbing or pressing Enter bypasses Data Validation -- am I left with figuring out how to disable the Tab & Enter keys, or with reexaming the cell entires when the user leaves this routine? Yuck! Since I'm in it this far, I also want to have the length of the entry be from 1 to 64 characters (it's a text field, alphanumeric OK) -- I had this part working already, but wanted to add the "non-empty" requirement as well, and now I'm stuck. If I could also enforce unique entries in the entire column, that woudl be ideal. I found a formula for this but it's not working. I think it is overly stringent, preventing even substrings, too, which is not what I want. Here's the data validation formulas I was using for uniqueness: This entry goes in DV in cell A1: =ISERROR(MATCH(A1,A2:A50,0)) and this one in cells A2:A9000 =ISERROR(MATCH(A2,INDIRECT("$A$1:$A$"&ROW()-1),0)) Your help is greatly appreciated, especailly at this late hour, <grins Will Finkle San Diego |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Mind-numbing simple data validation ... string from 1 to 64 ch
Gary's Student,
Thank you so much for the thoughtful posting of your Version 2 ! It indeed works like a charm. It's even almost *too* restrictive... just kidding... it's great. I've lots more to tackle before thsi project is complete, though, so I may be back later with questions on anything from ... --programmatic data validation, to --printing all the visible worksheets & charts in a workbook from a Forms 2.0 button on a worksheet, in 1 step.. or at least getting all the pages into Print Preview at the same time... --to using named ranges to move around columns of data several times, versus other methods I've heard of, like Lists or Arrays or Scenarios But yes, this issue is closed out & thanks again for your help. --Will Cyclometric "Gary''s Student" wrote: Hi Will: 1. First delete the old macro. €śB9€ť is just too €śbenign€ť! 2. In a standard module, enter: Public where_was_I As Range Sub startup() Set where_was_I = ActiveCell Application.EnableEvents = True End Sub The Public, static, variable where_was_I €śremembers€ť where we were before changing Selection. Startup initializes this variable. 3. In the worksheet code area, enter: Private Sub Worksheet_SelectionChange(ByVal Target As Range) ' ' Version 2 - The Sequel ' Dim on_a As Boolean Dim was_on_a As Boolean Set ra = Range("A:A") s = "You may not leave a column A cell empty" was_on_a = True If Intersect(where_was_I, ra) Is Nothing Then was_on_a = False End If If Target.Address = where_was_I.Address Then Exit Sub If was_on_a Then If IsEmpty(where_was_I) Then MsgBox (s) Application.EnableEvents = False where_was_I.Select Application.EnableEvents = True Exit Sub Else Set where_was_I = Target End If Else Set where_was_I = Target End If End Sub 4. Before changing Selection, run the startup macro The logic is a little funky. Whenever we move to a new cell, we look back to where we came from (previous cell). If the previous cell was on column A and that previous cell is empty, the warning is issued and the user is kicked back to that previous cell. Just remember to delete the old version prior to installing the new one. Update this post to let us know how you are doing. -- Gary''s Student - gsnu200745 "Will Finkle" wrote: Thanks, Gary, will do -- I appreciate your letting me know! :-) "Gary''s Student" wrote: Hi Will: Check back tomorrow. The Help Center is really backed up now. -- Gary''s Student - gsnu200745 "Will Finkle" wrote: Thanks, Gary's Student, for the quick response and the terrific help. I'm *almost* there. I've got the uniqueness code, no problem, thank you. But with the selection change sub, I have modified it thusly to activate the algorithm whenever the selection is a cell in the A column. But it's exiting the routing Here's the code I put in front, so it would only enter the routine for cells in the A column If InStr(1, ActiveCell.Address, "$A$") Then s = "You may not leave the Menu Item empty" Set r = ActiveCell End If But it's exiting out where it checks the intersection of Target & 'r', because got_there is true (so Exit Sub). If Not Intersect(Target, r) Is Nothing Then got_there = True Exit Sub End If So I backed up a step and just tried your code as written, on cell B9, and even though the code executed all the way through to the end, I was still able to tab to the next cell without the message box firing. Am I missing something? Thanks 1,000,000! --Will "Gary''s Student" wrote: Hi Will: This is only a single cell example of how to enforce data entry once a cell has been selected. The example uses cell B9. Once B9 has been Selected by either the mouse or the arrow keys or the ENTER key or TAB key, data must be entered before another cell can be Selected. This macro goes in the worksheet code area: Private Sub Worksheet_SelectionChange(ByVal Target As Range) Set r = Range("B9") s = "You may not leave B9 empty" If Target.Count 1 Then Exit Sub End If If Not Intersect(Target, r) Is Nothing Then got_there = True Exit Sub End If If got_there Then If IsEmpty(r) Then MsgBox (s) Application.EnableEvents = False r.Select Application.EnableEvents = True Exit Sub End If got_there = False End If End Sub The uniqueness thing is much easier. Say we have a table from Z3 to Z17 and only want unique entries. Set data validation in Z3 to FormulaIs: =COUNTIF($Z$3:$Z$17,Z3)<2 and copy the validation down the table -- Gary''s Student - gsnu200744 "Will Finkle" wrote: Hi, all, I've combed the discussion group as well as some of the great web resources for Excel programming & worksheet functions, and I've come to spend way too much time on what should be a simple data validation formula. First, I just wanted to prevent an empty cell in Column A. I noticed that data validation doesn't seem to kick in if you Tab or Enter over the cell -- DV only is working when the cursor's in the cell, by typing or clicking in cell or formular bar. I have tried the following custom validation rules without success, both with and without the $ before the A: =LEN($A)0 =NOT(ISBLANK($A) =NOT(EMPTY) They do work if I have the cursor in the cell, but again, Tabbing or pressing Enter bypasses Data Validation -- am I left with figuring out how to disable the Tab & Enter keys, or with reexaming the cell entires when the user leaves this routine? Yuck! Since I'm in it this far, I also want to have the length of the entry be from 1 to 64 characters (it's a text field, alphanumeric OK) -- I had this part working already, but wanted to add the "non-empty" requirement as well, and now I'm stuck. If I could also enforce unique entries in the entire column, that woudl be ideal. I found a formula for this but it's not working. I think it is overly stringent, preventing even substrings, too, which is not what I want. Here's the data validation formulas I was using for uniqueness: This entry goes in DV in cell A1: =ISERROR(MATCH(A1,A2:A50,0)) and this one in cells A2:A9000 =ISERROR(MATCH(A2,INDIRECT("$A$1:$A$"&ROW()-1),0)) Your help is greatly appreciated, especailly at this late hour, <grins Will Finkle San Diego |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Non VB (simple VB) Hyperlink Data Validation List | Excel Discussion (Misc queries) | |||
macro to bold number of chars from end of a string | Excel Discussion (Misc queries) | |||
search string command to answer simple Yes or No | Excel Worksheet Functions | |||
simple validation formula required | Excel Discussion (Misc queries) | |||
remove blanks from a string of chars within a cell? | Excel Discussion (Misc queries) |