How to display a blank cell with no error message

[Juco]

when i clear all the cells in the formula below I get #VALUE! showing in
the cell (M15)
Is there a way to just show a blank cell? and not have this message
showing.

thanks

=M7+M8+M13+M14

[Dave Peterson]

How do you clear the cells.

I'm guessing you hit the spacebar. That doesn't clear the cell--it puts in a
space character. (It may look the same, but it's not.)

Try clearing the cells by hitting the delete key on the keyboard.
(or edit|clear|contents)

Juco wrote:
>
> when i clear all the cells in the formula below I get #VALUE! showing in
> the cell (M15)
> Is there a way to just show a blank cell? and not have this message
> showing.
>
> thanks
>
> =M7+M8+M13+M14

--

Dave Peterson

[Juco]

Try and explain better...
cells M7 to M14 are totaled in cell M15
If I delete the contents of cells M7 to M14 I get this message in cell M15
#Value!
I think relating to the fact that the info in cells M7 to M14 doesn`t now
show any values.
As soon as I put a no back in the cells the message disappears.

thanks

"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
> How do you clear the cells.
>
> I'm guessing you hit the spacebar. That doesn't clear the cell--it puts
in a
> space character. (It may look the same, but it's not.)
>
> Try clearing the cells by hitting the delete key on the keyboard.
> (or edit|clear|contents)
>
> Juco wrote:
> >
> > when i clear all the cells in the formula below I get #VALUE!
showing in
> > the cell (M15)
> > Is there a way to just show a blank cell? and not have this message
> > showing.
> >
> > thanks
> >
> > =M7+M8+M13+M14
>
> --
>
> Dave Peterson

[JulieD]

Hi Juco

Like Dave i can only replicate the #VALUE error if i use the spacebar to
"delete" a value in any of the cells M7 to M14. Just pressing the delete
key on this range does not result in an error message when i try it - it
results in 0.

Are you typing numbers into these cells or are they the result of other
formulas?

How are you "deleting" the values. What version of excel?

Cheers
JulieD




"Juco" <[email protected]> wrote in message
news:[email protected]...
> Try and explain better...
> cells M7 to M14 are totaled in cell M15
> If I delete the contents of cells M7 to M14 I get this message in cell M15
> #Value!
> I think relating to the fact that the info in cells M7 to M14 doesn`t now
> show any values.
> As soon as I put a no back in the cells the message disappears.
>
> thanks
>
> "Dave Peterson" <[email protected]> wrote in message
> news:[email protected]...
>> How do you clear the cells.
>>
>> I'm guessing you hit the spacebar. That doesn't clear the cell--it puts
> in a
>> space character. (It may look the same, but it's not.)
>>
>> Try clearing the cells by hitting the delete key on the keyboard.
>> (or edit|clear|contents)
>>
>> Juco wrote:
>> >
>> > when i clear all the cells in the formula below I get #VALUE!
> showing in
>> > the cell (M15)
>> > Is there a way to just show a blank cell? and not have this message
>> > showing.
>> >
>> > thanks
>> >
>> > =M7+M8+M13+M14
>>
>> --
>>
>> Dave Peterson
>
>

[Ragdyer]

Describe *Exactly* how do you "Delete" the contents of your cells?

If you do as Dave suggests, select M7 to M14,
THEN, hit the delete key on the keyboard, your formula should return a zero
( 0 ).

If, on the other hand, the cells contain formulas and you're eliminating the
values in the cells calculated by the formulas, and are perhaps having the
formulas return a null ( "" ), you might try revising your formula to:

=SUM(M7:M14)
To have 0 returned,
OR
=IF(SUM(M7:M14),SUM(M7:M14),"")
To have null returned.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Juco" <[email protected]> wrote in message
news:[email protected]...
> Try and explain better...
> cells M7 to M14 are totaled in cell M15
> If I delete the contents of cells M7 to M14 I get this message in cell M15
> #Value!
> I think relating to the fact that the info in cells M7 to M14 doesn`t now
> show any values.
> As soon as I put a no back in the cells the message disappears.
>
> thanks
>
> "Dave Peterson" <[email protected]> wrote in message
> news:[email protected]...
> > How do you clear the cells.
> >
> > I'm guessing you hit the spacebar. That doesn't clear the cell--it puts
> in a
> > space character. (It may look the same, but it's not.)
> >
> > Try clearing the cells by hitting the delete key on the keyboard.
> > (or edit|clear|contents)
> >
> > Juco wrote:
> > >
> > > when i clear all the cells in the formula below I get #VALUE!
> showing in
> > > the cell (M15)
> > > Is there a way to just show a blank cell? and not have this message
> > > showing.
> > >
> > > thanks
> > >
> > > =M7+M8+M13+M14
> >
> > --
> >
> > Dave Peterson
>
>

[Ragdyer]

I just came across your other posts from yesterday pertaining to this form
you're working up, where here, you're trying to total the formula help you
received yesterday.

Why not post here the exact formulas that you've decided to use from
yesterday's suggestions.
Describe the formula in each of the cells that you're looking to sum.
Makes it easier for the folks around here to HELP YOU !
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------


"Juco" <[email protected]> wrote in message
news:[email protected]...
> Try and explain better...
> cells M7 to M14 are totaled in cell M15
> If I delete the contents of cells M7 to M14 I get this message in cell M15
> #Value!
> I think relating to the fact that the info in cells M7 to M14 doesn`t now
> show any values.
> As soon as I put a no back in the cells the message disappears.
>
> thanks
>
> "Dave Peterson" <[email protected]> wrote in message
> news:[email protected]...
> > How do you clear the cells.
> >
> > I'm guessing you hit the spacebar. That doesn't clear the cell--it puts
> in a
> > space character. (It may look the same, but it's not.)
> >
> > Try clearing the cells by hitting the delete key on the keyboard.
> > (or edit|clear|contents)
> >
> > Juco wrote:
> > >
> > > when i clear all the cells in the formula below I get #VALUE!
> showing in
> > > the cell (M15)
> > > Is there a way to just show a blank cell? and not have this message
> > > showing.
> > >
> > > thanks
> > >
> > > =M7+M8+M13+M14
> >
> > --
> >
> > Dave Peterson
>
>

[Juco]

Try and explain better...
cells M7 to M14 are totaled in cell M15
If I delete the contents of cells M7 to M14 I get this message in cell M15
#Value!
I think relating to the fact that the info in cells M7 to M14 doesn`t now
show any values.
As soon as I put a no. back in the cells the message disappears in M15

thanks
"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
> How do you clear the cells.
>
> I'm guessing you hit the spacebar. That doesn't clear the cell--it puts
in a
> space character. (It may look the same, but it's not.)
>
> Try clearing the cells by hitting the delete key on the keyboard.
> (or edit|clear|contents)
>
> Juco wrote:
> >
> > when i clear all the cells in the formula below I get #VALUE!
showing in
> > the cell (M15)
> > Is there a way to just show a blank cell? and not have this message
> > showing.
> >
> > thanks
> >
> > =M7+M8+M13+M14
>
> --
>
> Dave Peterson

[Juco]

This is the formulas I am using....
This in M15.......... M7+M8+M13+M14
This in M7........... =CHOOSE(MATCH(D7,{0;5;7;9},1),"",150,200,"")
This in M8........... =CHOOSE(MATCH(H7,{0;5;7;11},1),"",150,200,"")

It is a form for renting properties if the renter puts in the dates inD7 and
H7 then M7 and M8 calculate the rental for those weeks. The problem is if I
send a blank form with no dates entered it has #VALUE! where it would
normally enter the total in M15, the sheet works ok its just messy sending
out a blank form with #VALUE! on it what I would like is for those `error
messages to disappear` if possible.

I hope this is a bit clearer
thanks

"Ragdyer" <[email protected]> wrote in message
news:[email protected]...
> I just came across your other posts from yesterday pertaining to this form
> you're working up, where here, you're trying to total the formula help you
> received yesterday.
>
> Why not post here the exact formulas that you've decided to use from
> yesterday's suggestions.
> Describe the formula in each of the cells that you're looking to sum.
> Makes it easier for the folks around here to HELP YOU !
> --
> Regards,
>
> RD
>
> --------------------------------------------------------------------------
-
> Please keep all correspondence within the NewsGroup, so all may benefit !
> --------------------------------------------------------------------------
-
>
>
> "Juco" <[email protected]> wrote in message
> news:[email protected]...
> > Try and explain better...
> > cells M7 to M14 are totaled in cell M15
> > If I delete the contents of cells M7 to M14 I get this message in cell
M15
> > #Value!
> > I think relating to the fact that the info in cells M7 to M14 doesn`t
now
> > show any values.
> > As soon as I put a no back in the cells the message disappears.
> >
> > thanks
> >
> > "Dave Peterson" <[email protected]> wrote in message
> > news:[email protected]...
> > > How do you clear the cells.
> > >
> > > I'm guessing you hit the spacebar. That doesn't clear the cell--it
puts
> > in a
> > > space character. (It may look the same, but it's not.)
> > >
> > > Try clearing the cells by hitting the delete key on the keyboard.
> > > (or edit|clear|contents)
> > >
> > > Juco wrote:
> > > >
> > > > when i clear all the cells in the formula below I get #VALUE!
> > showing in
> > > > the cell (M15)
> > > > Is there a way to just show a blank cell? and not have this message
> > > > showing.
> > > >
> > > > thanks
> > > >
> > > > =M7+M8+M13+M14
> > >
> > > --
> > >
> > > Dave Peterson
> >
> >
>

[Dave Peterson]

I'm still confused at what you mean by clear. Do you mean your formula
evaluates to ""?

If yes, try this in M15:

=sum(m7,m8,m13,m14)

=sum() ignores cells with text. The add operator (+) likes to work with numbers
and has trouble with text.



Juco wrote:
>
> This is the formulas I am using....
> This in M15.......... M7+M8+M13+M14
> This in M7........... =CHOOSE(MATCH(D7,{0;5;7;9},1),"",150,200,"")
> This in M8........... =CHOOSE(MATCH(H7,{0;5;7;11},1),"",150,200,"")
>
> It is a form for renting properties if the renter puts in the dates inD7 and
> H7 then M7 and M8 calculate the rental for those weeks. The problem is if I
> send a blank form with no dates entered it has #VALUE! where it would
> normally enter the total in M15, the sheet works ok its just messy sending
> out a blank form with #VALUE! on it what I would like is for those `error
> messages to disappear` if possible.
>
> I hope this is a bit clearer
> thanks
>
> "Ragdyer" <[email protected]> wrote in message
> news:[email protected]...
> > I just came across your other posts from yesterday pertaining to this form
> > you're working up, where here, you're trying to total the formula help you
> > received yesterday.
> >
> > Why not post here the exact formulas that you've decided to use from
> > yesterday's suggestions.
> > Describe the formula in each of the cells that you're looking to sum.
> > Makes it easier for the folks around here to HELP YOU !
> > --
> > Regards,
> >
> > RD
> >
> > --------------------------------------------------------------------------
> -
> > Please keep all correspondence within the NewsGroup, so all may benefit !
> > --------------------------------------------------------------------------
> -
> >
> >
> > "Juco" <[email protected]> wrote in message
> > news:[email protected]...
> > > Try and explain better...
> > > cells M7 to M14 are totaled in cell M15
> > > If I delete the contents of cells M7 to M14 I get this message in cell
> M15
> > > #Value!
> > > I think relating to the fact that the info in cells M7 to M14 doesn`t
> now
> > > show any values.
> > > As soon as I put a no back in the cells the message disappears.
> > >
> > > thanks
> > >
> > > "Dave Peterson" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > How do you clear the cells.
> > > >
> > > > I'm guessing you hit the spacebar. That doesn't clear the cell--it
> puts
> > > in a
> > > > space character. (It may look the same, but it's not.)
> > > >
> > > > Try clearing the cells by hitting the delete key on the keyboard.
> > > > (or edit|clear|contents)
> > > >
> > > > Juco wrote:
> > > > >
> > > > > when i clear all the cells in the formula below I get #VALUE!
> > > showing in
> > > > > the cell (M15)
> > > > > Is there a way to just show a blank cell? and not have this message
> > > > > showing.
> > > > >
> > > > > thanks
> > > > >
> > > > > =M7+M8+M13+M14
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> > >
> > >
> >

--

Dave Peterson

[Ragdyer]

Try this in M15:

=SUM(M7:M8,M13:M14)
OR
=IF(SUM(M7:M8,M13:M14),SUM(M7:M8,M13:M14),"")
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Juco" <[email protected]> wrote in message
news:[email protected]...
> This is the formulas I am using....
> This in M15.......... M7+M8+M13+M14
> This in M7........... =CHOOSE(MATCH(D7,{0;5;7;9},1),"",150,200,"")
> This in M8........... =CHOOSE(MATCH(H7,{0;5;7;11},1),"",150,200,"")
>
> It is a form for renting properties if the renter puts in the dates inD7
and
> H7 then M7 and M8 calculate the rental for those weeks. The problem is if
I
> send a blank form with no dates entered it has #VALUE! where it would
> normally enter the total in M15, the sheet works ok its just messy
sending
> out a blank form with #VALUE! on it what I would like is for those
`error
> messages to disappear` if possible.
>
> I hope this is a bit clearer
> thanks
>
> "Ragdyer" <[email protected]> wrote in message
> news:[email protected]...
> > I just came across your other posts from yesterday pertaining to this
form
> > you're working up, where here, you're trying to total the formula help
you
> > received yesterday.
> >
> > Why not post here the exact formulas that you've decided to use from
> > yesterday's suggestions.
> > Describe the formula in each of the cells that you're looking to sum.
> > Makes it easier for the folks around here to HELP YOU !
> > --
> > Regards,
> >
> > RD
> >
>
> --------------------------------------------------------------------------
> -
> > Please keep all correspondence within the NewsGroup, so all may benefit
!
>
> --------------------------------------------------------------------------
> -
> >
> >
> > "Juco" <[email protected]> wrote in message
> > news:[email protected]...
> > > Try and explain better...
> > > cells M7 to M14 are totaled in cell M15
> > > If I delete the contents of cells M7 to M14 I get this message in cell
> M15
> > > #Value!
> > > I think relating to the fact that the info in cells M7 to M14 doesn`t
> now
> > > show any values.
> > > As soon as I put a no back in the cells the message disappears.
> > >
> > > thanks
> > >
> > > "Dave Peterson" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > How do you clear the cells.
> > > >
> > > > I'm guessing you hit the spacebar. That doesn't clear the cell--it
> puts
> > > in a
> > > > space character. (It may look the same, but it's not.)
> > > >
> > > > Try clearing the cells by hitting the delete key on the keyboard.
> > > > (or edit|clear|contents)
> > > >
> > > > Juco wrote:
> > > > >
> > > > > when i clear all the cells in the formula below I get #VALUE!
> > > showing in
> > > > > the cell (M15)
> > > > > Is there a way to just show a blank cell? and not have this
message
> > > > > showing.
> > > > >
> > > > > thanks
> > > > >
> > > > > =M7+M8+M13+M14
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> > >
> > >
> >
>
>

[Juco]

ok thanks that works good

thank you
Juco


"Ragdyer" <[email protected]> wrote in message
news:[email protected]...
> Try this in M15:
>
> =SUM(M7:M8,M13:M14)
> OR
> =IF(SUM(M7:M8,M13:M14),SUM(M7:M8,M13:M14),"")
> --
> HTH,
>
> RD
>
> --------------------------------------------------------------------------
-
> Please keep all correspondence within the NewsGroup, so all may benefit !
> --------------------------------------------------------------------------
-
> "Juco" <[email protected]> wrote in message
> news:[email protected]...
> > This is the formulas I am using....
> > This in M15.......... M7+M8+M13+M14
> > This in M7........... =CHOOSE(MATCH(D7,{0;5;7;9},1),"",150,200,"")
> > This in M8........... =CHOOSE(MATCH(H7,{0;5;7;11},1),"",150,200,"")
> >
> > It is a form for renting properties if the renter puts in the dates inD7
> and
> > H7 then M7 and M8 calculate the rental for those weeks. The problem is
if
> I
> > send a blank form with no dates entered it has #VALUE! where it would
> > normally enter the total in M15, the sheet works ok its just messy
> sending
> > out a blank form with #VALUE! on it what I would like is for those
> `error
> > messages to disappear` if possible.
> >
> > I hope this is a bit clearer
> > thanks
> >
> > "Ragdyer" <[email protected]> wrote in message
> > news:[email protected]...
> > > I just came across your other posts from yesterday pertaining to this
> form
> > > you're working up, where here, you're trying to total the formula help
> you
> > > received yesterday.
> > >
> > > Why not post here the exact formulas that you've decided to use from
> > > yesterday's suggestions.
> > > Describe the formula in each of the cells that you're looking to sum.
> > > Makes it easier for the folks around here to HELP YOU !
> > > --
> > > Regards,
> > >
> > > RD
> > >
> >
>
> --------------------------------------------------------------------------
> > -
> > > Please keep all correspondence within the NewsGroup, so all may
benefit
> !
> >
>
> --------------------------------------------------------------------------
> > -
> > >
> > >
> > > "Juco" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > Try and explain better...
> > > > cells M7 to M14 are totaled in cell M15
> > > > If I delete the contents of cells M7 to M14 I get this message in
cell
> > M15
> > > > #Value!
> > > > I think relating to the fact that the info in cells M7 to M14
doesn`t
> > now
> > > > show any values.
> > > > As soon as I put a no back in the cells the message disappears.
> > > >
> > > > thanks
> > > >
> > > > "Dave Peterson" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > How do you clear the cells.
> > > > >
> > > > > I'm guessing you hit the spacebar. That doesn't clear the
cell--it
> > puts
> > > > in a
> > > > > space character. (It may look the same, but it's not.)
> > > > >
> > > > > Try clearing the cells by hitting the delete key on the keyboard.
> > > > > (or edit|clear|contents)
> > > > >
> > > > > Juco wrote:
> > > > > >
> > > > > > when i clear all the cells in the formula below I get #VALUE!
> > > > showing in
> > > > > > the cell (M15)
> > > > > > Is there a way to just show a blank cell? and not have this
> message
> > > > > > showing.
> > > > > >
> > > > > > thanks
> > > > > >
> > > > > > =M7+M8+M13+M14
> > > > >
> > > > > --
> > > > >
> > > > > Dave Peterson
> > > >
> > > >
> > >
> >
> >
>

[RagDyeR]

Thanks for the feed-back.

If you had remained with your original thread from a couple of days ago, you
would have probably received a much quicker and accurate response.
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------

"Juco" <[email protected]> wrote in message
news:[email protected]...
ok thanks that works good

thank you
Juco


"Ragdyer" <[email protected]> wrote in message
news:[email protected]...
> Try this in M15:
>
> =SUM(M7:M8,M13:M14)
> OR
> =IF(SUM(M7:M8,M13:M14),SUM(M7:M8,M13:M14),"")
> --
> HTH,
>
> RD
>
> --------------------------------------------------------------------------
-
> Please keep all correspondence within the NewsGroup, so all may benefit !
> --------------------------------------------------------------------------
-
> "Juco" <[email protected]> wrote in message
> news:[email protected]...
> > This is the formulas I am using....
> > This in M15.......... M7+M8+M13+M14
> > This in M7........... =CHOOSE(MATCH(D7,{0;5;7;9},1),"",150,200,"")
> > This in M8........... =CHOOSE(MATCH(H7,{0;5;7;11},1),"",150,200,"")
> >
> > It is a form for renting properties if the renter puts in the dates inD7
> and
> > H7 then M7 and M8 calculate the rental for those weeks. The problem is
if
> I
> > send a blank form with no dates entered it has #VALUE! where it would
> > normally enter the total in M15, the sheet works ok its just messy
> sending
> > out a blank form with #VALUE! on it what I would like is for those
> `error
> > messages to disappear` if possible.
> >
> > I hope this is a bit clearer
> > thanks
> >
> > "Ragdyer" <[email protected]> wrote in message
> > news:[email protected]...
> > > I just came across your other posts from yesterday pertaining to this
> form
> > > you're working up, where here, you're trying to total the formula help
> you
> > > received yesterday.
> > >
> > > Why not post here the exact formulas that you've decided to use from
> > > yesterday's suggestions.
> > > Describe the formula in each of the cells that you're looking to sum.
> > > Makes it easier for the folks around here to HELP YOU !
> > > --
> > > Regards,
> > >
> > > RD
> > >
> >
>
> --------------------------------------------------------------------------
> > -
> > > Please keep all correspondence within the NewsGroup, so all may
benefit
> !
> >
>
> --------------------------------------------------------------------------
> > -
> > >
> > >
> > > "Juco" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > Try and explain better...
> > > > cells M7 to M14 are totaled in cell M15
> > > > If I delete the contents of cells M7 to M14 I get this message in
cell
> > M15
> > > > #Value!
> > > > I think relating to the fact that the info in cells M7 to M14
doesn`t
> > now
> > > > show any values.
> > > > As soon as I put a no back in the cells the message disappears.
> > > >
> > > > thanks
> > > >
> > > > "Dave Peterson" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > How do you clear the cells.
> > > > >
> > > > > I'm guessing you hit the spacebar. That doesn't clear the
cell--it
> > puts
> > > > in a
> > > > > space character. (It may look the same, but it's not.)
> > > > >
> > > > > Try clearing the cells by hitting the delete key on the keyboard.
> > > > > (or edit|clear|contents)
> > > > >
> > > > > Juco wrote:
> > > > > >
> > > > > > when i clear all the cells in the formula below I get #VALUE!
> > > > showing in
> > > > > > the cell (M15)
> > > > > > Is there a way to just show a blank cell? and not have this
> message
> > > > > > showing.
> > > > > >
> > > > > > thanks
> > > > > >
> > > > > > =M7+M8+M13+M14
> > > > >
> > > > > --
> > > > >
> > > > > Dave Peterson
> > > >
> > > >
> > >
> >
> >
>