 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
Mac Forum / Applications / Excel / July 2007Excel Blank Cell - Error Message "Must not be blank"
I have 2 worksheets, of which some cells are linked, to ensure the 2nd worksheet calcultes based on the data entry of the first worksheet, I want to ensure one cell is not blank, in other words remind the user to fill in the cell or it will not calculate or save the file?  SignatureTony One possibility: If the cell that needs to have an entry is, for example, A4 you can use a simple formula such as the following in an adjacent cell: =IF(ISBLANK(A4),"Data in A4 is REQUIRED!","") You might also use Conditional Formatting instead of or in addition to something like the above. You can also insert a Comment in the target cell or elsewhere or you can add a Text Box or other shape that displays a message advising the user accordingly. These are only a few possibilities, but if you want something more dynamic that actually prevents the file from being saved or closed - especially if you want an alert to display - you'll need to rely on VBA triggered by the occurence of those events. Perhaps somebody else can advise on that - code ain't my thing:-) SignatureHTH |:>) Bob Jones [MVP] Office:Mac >I have 2 worksheets, of which some cells are linked, to ensure the 2nd > worksheet calcultes based on the data entry of the first worksheet, I want > to > ensure one cell is not blank, in other words remind the user to fill in > the > cell or it will not calculate or save the file? Related to this: Is there any non-macro formula to make a cell completely blank? I'm wondering because of Excel's quirks when making a graph from a column of data. I'd love to be able to make the graph ignore a blank cell -- don't plot a point and draw a line directly between the two nearest nonblank values -- but entering values like #n/a or "" don't work. > One possibility: If the cell that needs to have an entry is, for example, A4 > you can use a simple formula such as the following in an adjacent cell: [quoted text clipped - 11 lines] > occurence of those events. Perhaps somebody else can advise on that - code > ain't my thing:-) SignatureTeam EM to the rescue! mailto:carl@Team-EM.com http://www.team-em.com Carl Thanks for your suggestion, I used Bobs If statement. SignatureTony > Related to this: > Is there any non-macro formula to make a cell completely blank? I'm [quoted text clipped - 18 lines] > > occurence of those events. Perhaps somebody else can advise on that - code > > ain't my thing:-) On 7/23/07 7:16 PM, in article carl-7B75E7.19163723072007@comcast.dca.giganews.com, "Carl Witthoft" <carl@witthoft.com> wrote: > Related to this: > Is there any non-macro formula to make a cell completely blank? I'm > wondering because of Excel's quirks when making a graph from a column of > data. I'd love to be able to make the graph ignore a blank cell -- > don't plot a point and draw a line directly between the two nearest > nonblank values -- but entering values like #n/a or "" don't work. If you use the formula =na() in a cell (or type #N/A) the value will not be plotted on a line graph, and the line will be drawn between the adjacent values. On a column chart, the value will be ignored and treated as though the cell was blank. SignatureBob Greenblatt [MVP], Macintosh bobgreenblattATmsnDOTcom Yes, that's correct, and I'm an idiot for asking the wrong question. What I meant to ask is: since a completely blank, cleared cell will lead to a break in the drawn line, i.e. not only is no point drawn, but the neighbors do NOT connect to each other, is there any way to set up a formula to produce this effect? (Somehow make the cell behave as though it's completely cleared even tho' there's a formula there). And, yeah, I'm working with scatterplots. Sorry for asking the easy case first :-( Carl > On 7/23/07 7:16 PM, in article > carl-7B75E7.19163723072007@comcast.dca.giganews.com, "Carl Witthoft" [quoted text clipped - 11 lines] > values. On a column chart, the value will be ignored and treated as though > the cell was blank. SignatureTeam EM to the rescue! mailto:carl@Team-EM.com http://www.team-em.com > Yes, that's correct, and I'm an idiot for asking the wrong question. > [quoted text clipped - 6 lines] > > Sorry for asking the easy case first :-( If you use Bob's suggestion (replace completely blank cells with =NA(), then scatterplots with lines connecting points will connect the points surrounding the #NA > > Yes, that's correct, and I'm an idiot for asking the wrong question. > > [quoted text clipped - 10 lines] > then scatterplots with lines connecting points will connect the points > surrounding the #NA Yep, I got that working. But there have been occasions (don't ask :-) ) when I really wanted to have a complete break in the line. SignatureTeam EM to the rescue! mailto:carl@Team-EM.com http://www.team-em.com On 7/25/07 7:58 PM, in article carl-E73726.19582425072007@comcast.dca.giganews.com, "Carl Witthoft" <carl@witthoft.com> wrote: >>> Yes, that's correct, and I'm an idiot for asking the wrong question. >>> [quoted text clipped - 13 lines] > Yep, I got that working. But there have been occasions (don't ask :-) > ) when I really wanted to have a complete break in the line. Am I missing something? If you want a break in the line, leave the cell as blank. Sounds like you want both to happen. SignatureBob Greenblatt [MVP], Macintosh bobgreenblattATmsnDOTcom > >> If you use Bob's suggestion (replace completely blank cells with =NA(), > >> then scatterplots with lines connecting points will connect the points [quoted text clipped - 4 lines] > Am I missing something? If you want a break in the line, leave the cell as > blank. Sounds like you want both to happen. Well, I was hoping to be able to evaluate my data (say in a neighboring column) to generate the column of data I wanted to plot, and to include blank cells (or equivalently, breaks in the line) automatically rather than having to go back and manually clear out the cells I don't "like." As I think I mentioned, I could write a macro to execute the "clear" command, but that's always ugly (and people panic if I give them spreadsheets with macros in them :-( ) SignatureTeam EM to the rescue! mailto:carl@Team-EM.com http://www.team-em.com On 7/26/07 5:01 PM, in article carl-C08D94.17010626072007@comcast.dca.giganews.com, "Carl Witthoft" <carl@witthoft.com> wrote: >>>> If you use Bob's suggestion (replace completely blank cells with =NA(), >>>> then scatterplots with lines connecting points will connect the points [quoted text clipped - 13 lines] > command, but that's always ugly (and people panic if I give them > spreadsheets with macros in them :-( ) In your new column that will be plotted, try a formula like: =If(whatever,"",data). This will leave the cell blank. However, since it has a formula it may not plot as you like and you will have to blank it manually, or via VBA. SignatureBob Greenblatt [MVP], Macintosh bobgreenblattATmsnDOTcom As long as you select blank cells as a part of the range to be plotted they will "take up space" in the chart - regardless of what content [or lack of same] they may have. The only way I know of to do what I understand you to be looking for is to Command+Click each individual cell in the column that *does* contain a value to be plotted, then generate the chart. SignatureHTH |:>) Bob Jones [MVP] Office:Mac > Related to this: > Is there any non-macro formula to make a cell completely blank? I'm [quoted text clipped - 24 lines] >> code >> ain't my thing:-) Bob Thanks I used your If statement, it works! SignatureTony > One possibility: If the cell that needs to have an entry is, for example, A4 > you can use a simple formula such as the following in an adjacent cell: [quoted text clipped - 18 lines] > > the > > cell or it will not calculate or save the file? |
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.