Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Validation to restrict blank value in adjacent field
Team,
Try this: In cell A1, add following "custom" validation: =not(isblank(b1)) As I see it, this should restrict leaving cell A1 unless cell B1 already has a value. I've also tried =b1<"" and some others. Should be simple but I can't get it to work. Validation will not fire. For each equation I've tried, I've cut and pasted the same equation into a field and it works fine. Thanks. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Validation to restrict blank value in adjacent field
Your first works, so does your second. You just need to deselect "ignore
blank" in the setup dialogue box -- Regards, Peo Sjoblom "John S. Labarge" <John S. wrote in message ... Team, Try this: In cell A1, add following "custom" validation: =not(isblank(b1)) As I see it, this should restrict leaving cell A1 unless cell B1 already has a value. I've also tried =b1<"" and some others. Should be simple but I can't get it to work. Validation will not fire. For each equation I've tried, I've cut and pasted the same equation into a field and it works fine. Thanks. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Validation to restrict blank value in adjacent field
In the data validation dialog box, remove the check mark from Ignore Blank.
John S. Labarge wrote: In cell A1, add following "custom" validation: =not(isblank(b1)) As I see it, this should restrict leaving cell A1 unless cell B1 already has a value. I've also tried =b1<"" and some others. Should be simple but I can't get it to work. Validation will not fire. For each equation I've tried, I've cut and pasted the same equation into a field and it works fine. -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Validation to restrict blank value in adjacent field
Peo and Debra,
Thanks to both of you for your responses. I'm just trying to understand this to forestall my embarrassment of such an easy solution. I interpreted "Ignore blank" as "Do not fire the validation in this field should you happen to be leaving this field while it is blank." If one interprets it as such, this solution, while I see it works, shouldn't make a difference. Please explain how you two knew this. Thanks again. "Debra Dalgleish" wrote: In the data validation dialog box, remove the check mark from Ignore Blank. John S. Labarge wrote: In cell A1, add following "custom" validation: =not(isblank(b1)) As I see it, this should restrict leaving cell A1 unless cell B1 already has a value. I've also tried =b1<"" and some others. Should be simple but I can't get it to work. Validation will not fire. For each equation I've tried, I've cut and pasted the same equation into a field and it works fine. -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Validation to restrict blank value in adjacent field
In some cases, you may want blank cells to be ignored, so you'd leave
the check mark in the Ignore Blank option. In your example, you want to check for a blank cell, and prevent an entry in cell A1 if cell B1 is blank. So, you turn off the Ignore Blank option so the data validation can test for the blank cell. John S. Labarge wrote: Peo and Debra, Thanks to both of you for your responses. I'm just trying to understand this to forestall my embarrassment of such an easy solution. I interpreted "Ignore blank" as "Do not fire the validation in this field should you happen to be leaving this field while it is blank." If one interprets it as such, this solution, while I see it works, shouldn't make a difference. Please explain how you two knew this. Thanks again. "Debra Dalgleish" wrote: In the data validation dialog box, remove the check mark from Ignore Blank. John S. Labarge wrote: In cell A1, add following "custom" validation: =not(isblank(b1)) As I see it, this should restrict leaving cell A1 unless cell B1 already has a value. I've also tried =b1<"" and some others. Should be simple but I can't get it to work. Validation will not fire. For each equation I've tried, I've cut and pasted the same equation into a field and it works fine. -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Validation to restrict blank value in adjacent field
I am sure I learned it through trial and error. I remember I thought I was
smart when I came up with this solution =OFFSET(A1,,1)<"" which will work without deselecting the check box. Then I found out about removing the check mark Many people interpret it as though you can force a user to enter something in the cell with validation if you leave it remove the check mark . Frankly I don't think I even noticed it when I started using validation. -- Regards, Peo Sjoblom "John S. Labarge" wrote in message ... Peo and Debra, Thanks to both of you for your responses. I'm just trying to understand this to forestall my embarrassment of such an easy solution. I interpreted "Ignore blank" as "Do not fire the validation in this field should you happen to be leaving this field while it is blank." If one interprets it as such, this solution, while I see it works, shouldn't make a difference. Please explain how you two knew this. Thanks again. "Debra Dalgleish" wrote: In the data validation dialog box, remove the check mark from Ignore Blank. John S. Labarge wrote: In cell A1, add following "custom" validation: =not(isblank(b1)) As I see it, this should restrict leaving cell A1 unless cell B1 already has a value. I've also tried =b1<"" and some others. Should be simple but I can't get it to work. Validation will not fire. For each equation I've tried, I've cut and pasted the same equation into a field and it works fine. -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I un-restrict my drop down list field on a template? | Excel Discussion (Misc queries) | |||
How to restrict entry or color the field | Excel Discussion (Misc queries) | |||
using data validation and inserting more than one item in a field | Excel Discussion (Misc queries) | |||
Input message on data validation field | Excel Discussion (Misc queries) | |||
Excel2000: Data Validation to restrict entries | Excel Discussion (Misc queries) |