Excel Blank Cell - Error Message "Must not be blank"

[TonyWeston]

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?

 

[CyberTaz]

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

 

[Carl Witthoft]

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.

 

[TonyWeston]

Carl

Thanks for your suggestion, I used Bobs If statement.

 

[TonyWeston]

Bob

Thanks I used your If statement, it works!

 

[Bob Greenblatt]

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.

 

[CyberTaz]

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.

 

[Carl Witthoft]

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

 

[JE McGimpsey]

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 :-(

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

 

[Carl Witthoft]

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

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.

 

[Bob Greenblatt]

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.

 

[Carl Witthoft]

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.[/QUOTE]

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 :-( )

 

[Bob Greenblatt]

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 :-( )[/QUOTE]
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.