 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / April 2008Data Validation - Allow "" in Number Cell
It's amazing how much time can be wasted trying variations of a custom formula in Data Validation without hitting on the right syntax :-( I have a cell (assume A1) formatted as a number to 2 decimal places. Normally a formula will enter the appropriate number in the cell, otherwise the formula will enter "". Sometimes the formula will be overwritten and a number will be entered manually. Because the number may be entered manually, I want to set a Data Validation check. Data Validation will need to allow both "" or any number (including 0.00). I was trying to use the ISNUMBER function in Data Validation custom - something like: =AND(A1<>"",NOT(ISNUMBER(A1))) but it doesn't seem to work. Can someone point me in the right direction? TIA  SignatureMike -Please remove 'safetycatch' from email address before firing off your reply- just use a custom data validation but check (tick) the ignore blank box The custom validation should be =ISNUMBER(A1) Regards Gerry > It's amazing how much time can be wasted trying variations of a custom > formula in Data Validation without hitting on the right syntax :-( [quoted text clipped - 19 lines] > > TIA "GerryGerry" wrotewrote: > just use a custom data validation but check (tick) the > ignore blank box. Hi Gerry I don't think that will work. AFAIK, Data Validation does not see "" as a blank, and consequently the function fails when the formula tries to enter "". I did ask in a previous post whether a formula can be made to enter a true blank, rather than "", but I never got a response. SignatureMike -Please remove 'safetycatch' from email address before firing off your reply- You can make the cell appear empty by returning a zero length string (=""). But the cell can't be empty if it contains a formula. I don't understand how you're going to use data|Validation and a formula in that cell at the same time. > "GerryGerry" wrotewrote: > > [quoted text clipped - 14 lines] > -Please remove 'safetycatch' from email address before firing off your > reply- SignatureDave Peterson You can type the formula in the cell once the data validation is in place. It will only accept the formula if the formula result passes the validation criteria. > You can make the cell appear empty by returning a zero length string > (=""). But [quoted text clipped - 23 lines] >> -Please remove 'safetycatch' from email address before firing off your >> reply- And when the formula recalculates and doesn't meet the data|validation rules, what happens? I still don't get it. > You can type the formula in the cell once the data validation is in place. > It will only accept the formula if the formula result passes the validation [quoted text clipped - 30 lines] > > > > Dave Peterson SignatureDave Peterson > And when the formula recalculates and doesn't meet the > data|validation rules, what happens? Then the Data Validation error message box is displayed and you're asked to correct the cell entry. This might mean modifying the formula to give the correct result, or modifying the Data Validation rules to accept what the formula is returning. It seems that the cell formula and the associated data validation can be entered in either order. When data validation is initially set, it ignores anything that is already in the cell, including formulas. The data validation checks the cell content at the next refresh/recalculation and throws up an error message if the cell content conflicts with the validation criteria. If the cell content is a formula that returns a valid result (as allowed by the data validation settings), then there is no conflict and the result is displayed. SignatureMike -Please remove 'safetycatch' from email address before firing off your reply- "mlv" <mike.safetycatchvinc...@jet.co.uk> wrote... >>And when the formula recalculates and doesn't meet the >>data|validation rules, what happens? > >Then the Data Validation error message box is displayed and you're >asked to correct the cell entry. . . . Wrong! Data validation is ONLY triggered on cell ENTRY, not on recalculation of a cell's value. Simple test: give cell A1 the validation formula =AND(A1>=0,A1<=10). Enter 5 in A1, no problem. Enter 500 in A1, it doesn't accept the entry. Enter =B1 in A1, no problem because empty B1 would be evaluated as numeric 0. Now change B1 to 1000. What happens? Don't guess this time, try it! Harlan Grove chastised: >> I wrote: >> [quoted text clipped - 9 lines] > Data validation is ONLY triggered on cell ENTRY, not on > recalculation of a cell's value. Hmm, I think I see what you mean. To get the formula accepted at inception, it has to return a value that is accepted by the Data Validation criteria (if Data Validation is set). After that, it doesn't seem to matter and any value is accepted and displayed in the cell. I had assumed that recalculation of a cell value by a formula would be the equivalent of a new cell entry. Why wouldn't it be? > Don't guess this time, try it! I didn't guess last time. I was testing by reinitialising the formula, so Data Validation was triggered every time and I got the results I reported. Because I was using reinitialisation of the formula, I didn't pick up on the anomaly that a change of value in a cell is not validation checked when it is the result of a formula recalculation. Why, when a formula recalculates and changes a cell's value, isn't it considered to be a new cell entry, just as if someone had manually entered a new value? It seems logical (to me) that it should be. Perhaps I'm missing something here? Now I've got to rethink my approach. Guess I have to incorporate any necessary data validation into the formula instead. SignatureMike -Please remove 'safetycatch' from email address before firing off your reply- "mlv" <mike.safetycatchvinc...@jet.co.uk> wrote... ... >I had assumed that recalculation of a cell value by a formula would >be the equivalent of a new cell entry. Why wouldn't it be? ... Just because it isn't. Excel distinguishes between cell entry and cell evaluation. That's just the way it's designed. Besides, data validation is a toy feature. It's easily bypassed by pasting plain text into cells. For example, in an new workbook give cell A1 the data validation formula =A1>=0. Now copy the following nonblank line from this newsgroup posting -100 switch back to Excel, move to this particular cell and press [Ctrl]+V. And to add to Harlan's response... If you really want to use something that checks for valid entries as the result of formulas, you can either use an adjacent cell (format the error message in a big, bold, red font). Or you could use a worksheet event -- worksheet_calculation if the cell contains a formula or worksheet_change if the cell is changed by typing/pasting. If macros are disabled (or events disabled), then the event suggestion won't work. If calculation is set to manual, you may not like the adjacent cell. But I don't see how data|validation fits into your requirements. > > And when the formula recalculates and doesn't meet the > > data|validation rules, what happens? [quoted text clipped - 19 lines] > -Please remove 'safetycatch' from email address before firing off your > reply- SignatureDave Peterson > But I don't see how data|validation fits into your requirements. Basically it was a consequence of having to allow users of a particular Worksheet to overwrite a formula under certain circumstances and to directly enter a value into the cell themselves. Data Validation was set to ensure the user manually entered valid data. I have actually got the formula and Data Validation working together just fine now. The problem was persuading Data Validation to allow the formula to initialise when it was trying to enter "" in the cell. Data Validation doesn't seem to recognise "" as a blank, so ticking 'Ignore Blank' on the Data Validation dialogue box didn't work. However, I'm going to modify the Worksheet concerned (and the formula), so that the user can enter the override data into a separate cell outside of the print area, from where the formula can pick it up. That way Data Validation is not required for the cell with the formula, but can be set for the override cell instead. SignatureMike -Please remove 'safetycatch' from email address before firing off your reply- When I want to give the user the option of using a result of a formula or override that formula, I'll use 3 cells. Say A1, B1, and C1 A1 would contain the formula (and lock the cell and protect the sheet) B1 would be for the override C1 would be a formula that determines which cell should be used (also locked) =if(b1="",a1,b1) > > But I don't see how data|validation fits into your requirements. > [quoted text clipped - 18 lines] > -Please remove 'safetycatch' from email address before firing off your > reply- SignatureDave Peterson > When I want to give the user the option of using a result > of a formula or override that formula, I'll use 3 cells. [quoted text clipped - 6 lines] > > =if(b1="",a1,b1) Thanks Dave. That's basically what I intend to do, but only using 2 cells. Assume A1 & R1. A1 is part of the printed Worksheet. R1 is outside of the printed area. A1 contains the formula (cell locked and protected) R1 is for the manual override (formatted as appropriate and Data Validation set) Formula (in A1) would be: =IF(R1="",<formula-do whatever>,R1) Seems to work. Is there an advantage to the 3 cell scheme? SignatureMike -Please remove 'safetycatch' from email address before firing off your reply- Just that you can see what each option provides. > > When I want to give the user the option of using a result > > of a formula or override that formula, I'll use 3 cells. [quoted text clipped - 27 lines] > -Please remove 'safetycatch' from email address before firing off your > reply- SignatureDave Peterson OK, I think I've got there. The Data Validation custom formula seems to work with: =OR(A1="",ISNUMBER(A1)) Guess I originally had things AAF. SignatureMike -Please remove 'safetycatch' from email address before firing off your reply- |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.