I know it is possible to show zero values in a sheet by ticking "zero
values" in options, but I have a situation where zero values should only be
shown in certain areas of the sheet.

I've tried setting formatting to text and this displays zero values entered,
but it plays havoc with the data validation (Decimal greater than or equal
to 0).

Is there any way I can retain operation of the data validation, but still
display zero values in a specific range?

--
Ian
--

The only way I can think of is to use the option "show zero values"; then in
the area where zeros are not to be shown forma the cell with something like
#;#,"" to hide zeros
best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"IanC" wrote in message
...
I know it is possible to show zero values in a sheet by ticking "zero
values" in options, but I have a situation where zero values should only
be shown in certain areas of the sheet.

I've tried setting formatting to text and this displays zero values
entered, but it plays havoc with the data validation (Decimal greater than
or equal to 0).

Is there any way I can retain operation of the data validation, but still
display zero values in a specific range?

--
Ian
--

Use custom number formatting, to which there are 4 components, each separated
by a semi-colon

[positive];[negative];[zero];text

You get there by selecting the cells you want to format and pressing Ctrl-1

To show only positive and negative, use something like

#,##0;-#,##0;

To show positive, negative, and zeros, use something like

#,##0;-#,##0;0 or #,##0;-#,##0 [notice there's no trailing semi-colon]



"IanC" wrote:

I know it is possible to show zero values in a sheet by ticking "zero
values" in options, but I have a situation where zero values should only be
shown in certain areas of the sheet.

I've tried setting formatting to text and this displays zero values entered,
but it plays havoc with the data validation (Decimal greater than or equal
to 0).

Is there any way I can retain operation of the data validation, but still
display zero values in a specific range?

--
Ian
--


.

Another possibility is to leave all zeros shown and use Accounting format for
all of the areas where you don't want the zero digit displayed. With this
format zero displays as -. this lets you know something is being calculated
there, but does not fill up a sheet with 0 digits.

You can also create you own custom format and use - for zero, but the
Accounting format is standard and easy.
--
If this helps, please remember to click yes.


"IanC" wrote:

I know it is possible to show zero values in a sheet by ticking "zero
values" in options, but I have a situation where zero values should only be
shown in certain areas of the sheet.

I've tried setting formatting to text and this displays zero values entered,
but it plays havoc with the data validation (Decimal greater than or equal
to 0).

Is there any way I can retain operation of the data validation, but still
display zero values in a specific range?

--
Ian
--


.

Thanks to Bernard, Duke and Paul. I didn't realise there could be so many
options for something this simple! Thanks for your suggestions.

Paul - I've discounted your method as it shows - even if nothing has been
entered.
Duke & Bernard - I've used a variation of your methods but have a question.
In some cases the entries may be integers or 1dp so I've gone for #.#;-#.#;0
The problem with this is that when a decimal point is still shown when an
integer is entered (eg 1. instead of 1). Whilst it's not a big problem,
aesthetically I'd prefer to lose the point.

Any ideas?

--
Ian
--

"IanC" wrote in message
...
I know it is possible to show zero values in a sheet by ticking "zero
values" in options, but I have a situation where zero values should only be
shown in certain areas of the sheet.

I've tried setting formatting to text and this displays zero values
entered, but it plays havoc with the data validation (Decimal greater than
or equal to 0).

Is there any way I can retain operation of the data validation, but still
display zero values in a specific range?

--
Ian
--