ExcelBanter - ##### in cells

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - ##### in cells (https://www.excelbanter.com/excel-discussion-misc-queries/139923-cells.html)

How do I stop ##### from displaying in formula cells?

Min

Widen the column or do not try and display negative times

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
web: www.nickhodge.co.uk
blog: www.nickhodge.co.uk/blog/

FREE UK OFFICE USER GROUP MEETING, MS READING, 27th APRIL 2007
www.officeusergroup.co.uk

"Min" wrote in message
...
How do I stop ##### from displaying in formula cells?

My formula is based on subtracting the value of 2 different cells. If no data
has been entered into one of the cells then I get the #value! result. How can
I either hide this or display it as some meaningful text. Can I use
error.type?

"Nick Hodge" wrote:

Min

Widen the column or do not try and display negative times

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
web: www.nickhodge.co.uk
blog: www.nickhodge.co.uk/blog/

FREE UK OFFICE USER GROUP MEETING, MS READING, 27th APRIL 2007
www.officeusergroup.co.uk

"Min" wrote in message
...
How do I stop ##### from displaying in formula cells?

You have a few options:

One is to rewrite your formula:
=if(count(a1:b1)=0,"",a1-b1)
or
=if(count(a1:b1)=0,"Please enter at least one number!",a1-b1)
(=count() counts the quantity of numbers in that range.)

Another option is to use format|Conditional formatting to hide the error.
format|conditional formatting
formula is: =iserror(c1)
give it a white font on white fill (match the font color with the fill color)

Min wrote:

My formula is based on subtracting the value of 2 different cells. If no data
has been entered into one of the cells then I get the #value! result. How can
I either hide this or display it as some meaningful text. Can I use
error.type?

"Nick Hodge" wrote:

Min

Widen the column or do not try and display negative times

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
web: www.nickhodge.co.uk
blog: www.nickhodge.co.uk/blog/

FREE UK OFFICE USER GROUP MEETING, MS READING, 27th APRIL 2007
www.officeusergroup.co.uk

"Min" wrote in message
...
How do I stop ##### from displaying in formula cells?

--

Dave Peterson