Excel 2003.
I'm working on a workbook where the cells on Sheet 1 all have the same
drop-down list (contains numbers - eg 0.5, 1, 1.5, 2, 2.5, 7.5, 10, 12.5, 20
& 25).
In this sheet I'm trying to format the cells with the drop-downs to show the
number as selected but what happens is that excel rounds up the .5 (12.5
becomes 13 etc).
When I format the cells to Number with one decimal place it's OK for the
decimals but I would like it to just show 20 (not 20.0) is there a way to do
this?

(I've tried formatting the cells to Text and General - these both round Up
the .5
I've formated the sourse of the Drop-down list as TEXT).
The TOTALS column on this sheet is fomatted to General & this displays as
wanted.
The TOTALS are e.g. =SUM(B4:T4).

Sheet 2 of this book is layed out the same as Sheet 1 and the values entered
into sheet 1 are copied to sheet 2 using the Absolute Cell Ref $C$R. This
sheet gets sorted by TOTAL so this sheet is an Update sheet.
I'm having the same problem with this sheet as given above.
Will the Fix for Sheet 1 also fix Sheet 2 or will I need something else?

As another question, how can I get Sheet 2 to NOT show 0 when cells in Sheet
1 are empty? (I've got this sheet formatterd as Number right now and each
cell has ='SHEET 1'!$C$R reffering to it's corresponding cell on Sheet 1).

Thanks for any assistance
John

Try widening the columnwidth (or use a smaller font).

But keep the format for the cell General.

Originalgoth wrote:
>
> Excel 2003.
> I'm working on a workbook where the cells on Sheet 1 all have the same
> drop-down list (contains numbers - eg 0.5, 1, 1.5, 2, 2.5, 7.5, 10, 12.5, 20
> & 25).
> In this sheet I'm trying to format the cells with the drop-downs to show the
> number as selected but what happens is that excel rounds up the .5 (12.5
> becomes 13 etc).
> When I format the cells to Number with one decimal place it's OK for the
> decimals but I would like it to just show 20 (not 20.0) is there a way to do
> this?
>
> (I've tried formatting the cells to Text and General - these both round Up
> the .5
> I've formated the sourse of the Drop-down list as TEXT).
> The TOTALS column on this sheet is fomatted to General & this displays as
> wanted.
> The TOTALS are e.g. =SUM(B4:T4).
>
> Sheet 2 of this book is layed out the same as Sheet 1 and the values entered
> into sheet 1 are copied to sheet 2 using the Absolute Cell Ref $C$R. This
> sheet gets sorted by TOTAL so this sheet is an Update sheet.
> I'm having the same problem with this sheet as given above.
> Will the Fix for Sheet 1 also fix Sheet 2 or will I need something else?
>
> As another question, how can I get Sheet 2 to NOT show 0 when cells in Sheet
> 1 are empty? (I've got this sheet formatterd as Number right now and each
> cell has ='SHEET 1'!$C$R reffering to it's corresponding cell on Sheet 1).
>
> Thanks for any assistance
> John

--

Dave Peterson

Question 1 - Conditionally show the decimal. So long as you are not looking
to have the comma then you could just format as general. If you do need the
coma then reply back.

Question 2 - How to show blank instead of zero. Try a formula like this
possibly.
=if(A1 = "", "", A1)

or a custom format
#,##0;-#,##0;

or Tools | Options | View -> Uncheck Zero Values

--
HTH...

Jim Thomlinson


"Originalgoth" wrote:

> Excel 2003.
> I'm working on a workbook where the cells on Sheet 1 all have the same
> drop-down list (contains numbers - eg 0.5, 1, 1.5, 2, 2.5, 7.5, 10, 12.5, 20
> & 25).
> In this sheet I'm trying to format the cells with the drop-downs to show the
> number as selected but what happens is that excel rounds up the .5 (12.5
> becomes 13 etc).
> When I format the cells to Number with one decimal place it's OK for the
> decimals but I would like it to just show 20 (not 20.0) is there a way to do
> this?
>
> (I've tried formatting the cells to Text and General - these both round Up
> the .5
> I've formated the sourse of the Drop-down list as TEXT).
> The TOTALS column on this sheet is fomatted to General & this displays as
> wanted.
> The TOTALS are e.g. =SUM(B4:T4).
>
> Sheet 2 of this book is layed out the same as Sheet 1 and the values entered
> into sheet 1 are copied to sheet 2 using the Absolute Cell Ref $C$R. This
> sheet gets sorted by TOTAL so this sheet is an Update sheet.
> I'm having the same problem with this sheet as given above.
> Will the Fix for Sheet 1 also fix Sheet 2 or will I need something else?
>
> As another question, how can I get Sheet 2 to NOT show 0 when cells in Sheet
> 1 are empty? (I've got this sheet formatterd as Number right now and each
> cell has ='SHEET 1'!$C$R reffering to it's corresponding cell on Sheet 1).
>
> Thanks for any assistance
> John
>

Thanks Dave,
Yes, making the font size smaller did work when I formatted cells as General.

I've also taken Jims advice & Unchecked the "Zero Values" box.

I've clicked the YES for both of you.

Regards
John


"Dave Peterson" wrote:

> Try widening the columnwidth (or use a smaller font).
>
> But keep the format for the cell General.
>
> Originalgoth wrote:
> >
> > Excel 2003.
> > I'm working on a workbook where the cells on Sheet 1 all have the same
> > drop-down list (contains numbers - eg 0.5, 1, 1.5, 2, 2.5, 7.5, 10, 12.5, 20
> > & 25).
> > In this sheet I'm trying to format the cells with the drop-downs to show the
> > number as selected but what happens is that excel rounds up the .5 (12.5
> > becomes 13 etc).
> > When I format the cells to Number with one decimal place it's OK for the
> > decimals but I would like it to just show 20 (not 20.0) is there a way to do
> > this?
> >
> > (I've tried formatting the cells to Text and General - these both round Up
> > the .5
> > I've formated the sourse of the Drop-down list as TEXT).
> > The TOTALS column on this sheet is fomatted to General & this displays as
> > wanted.
> > The TOTALS are e.g. =SUM(B4:T4).
> >
> > Sheet 2 of this book is layed out the same as Sheet 1 and the values entered
> > into sheet 1 are copied to sheet 2 using the Absolute Cell Ref $C$R. This
> > sheet gets sorted by TOTAL so this sheet is an Update sheet.
> > I'm having the same problem with this sheet as given above.
> > Will the Fix for Sheet 1 also fix Sheet 2 or will I need something else?
> >
> > As another question, how can I get Sheet 2 to NOT show 0 when cells in Sheet
> > 1 are empty? (I've got this sheet formatterd as Number right now and each
> > cell has ='SHEET 1'!$C$R reffering to it's corresponding cell on Sheet 1).
> >
> > Thanks for any assistance
> > John
>
> --
>
> Dave Peterson
> .
>

Thanks Jim,
Between you & Dave Peterson you've both helped me out Greatly.

I've Un-checked the Zero Values box as you said.

Thanks for your help

I've clicked YES to show that your post helped me.

regards
John


"Jim Thomlinson" wrote:

> Question 1 - Conditionally show the decimal. So long as you are not looking
> to have the comma then you could just format as general. If you do need the
> coma then reply back.
>
> Question 2 - How to show blank instead of zero. Try a formula like this
> possibly.
> =if(A1 = "", "", A1)
>
> or a custom format
> #,##0;-#,##0;
>
> or Tools | Options | View -> Uncheck Zero Values
>
> --
> HTH...
>
> Jim Thomlinson
>
>
> "Originalgoth" wrote:
>
> > Excel 2003.
> > I'm working on a workbook where the cells on Sheet 1 all have the same
> > drop-down list (contains numbers - eg 0.5, 1, 1.5, 2, 2.5, 7.5, 10, 12.5, 20
> > & 25).
> > In this sheet I'm trying to format the cells with the drop-downs to show the
> > number as selected but what happens is that excel rounds up the .5 (12.5
> > becomes 13 etc).
> > When I format the cells to Number with one decimal place it's OK for the
> > decimals but I would like it to just show 20 (not 20.0) is there a way to do
> > this?
> >
> > (I've tried formatting the cells to Text and General - these both round Up
> > the .5
> > I've formated the sourse of the Drop-down list as TEXT).
> > The TOTALS column on this sheet is fomatted to General & this displays as
> > wanted.
> > The TOTALS are e.g. =SUM(B4:T4).
> >
> > Sheet 2 of this book is layed out the same as Sheet 1 and the values entered
> > into sheet 1 are copied to sheet 2 using the Absolute Cell Ref $C$R. This
> > sheet gets sorted by TOTAL so this sheet is an Update sheet.
> > I'm having the same problem with this sheet as given above.
> > Will the Fix for Sheet 1 also fix Sheet 2 or will I need something else?
> >
> > As another question, how can I get Sheet 2 to NOT show 0 when cells in Sheet
> > 1 are empty? (I've got this sheet formatterd as Number right now and each
> > cell has ='SHEET 1'!$C$R reffering to it's corresponding cell on Sheet 1).
> >
> > Thanks for any assistance
> > John
> >

I didn't notice the second question.

But I'd use Jim's first suggestion:
=if('SHEET 1'!A1="","",'sheet 1'!a1)

You may actually want to see when real 0's are brought back--or other
formulas/values are really 0.



Originalgoth wrote:
>
> Thanks Dave,
> Yes, making the font size smaller did work when I formatted cells as General.
>
> I've also taken Jims advice & Unchecked the "Zero Values" box.
>
> I've clicked the YES for both of you.
>
> Regards
> John
>
> "Dave Peterson" wrote:
>
> > Try widening the columnwidth (or use a smaller font).
> >
> > But keep the format for the cell General.
> >
> > Originalgoth wrote:
> > >
> > > Excel 2003.
> > > I'm working on a workbook where the cells on Sheet 1 all have the same
> > > drop-down list (contains numbers - eg 0.5, 1, 1.5, 2, 2.5, 7.5, 10, 12.5, 20
> > > & 25).
> > > In this sheet I'm trying to format the cells with the drop-downs to show the
> > > number as selected but what happens is that excel rounds up the .5 (12.5
> > > becomes 13 etc).
> > > When I format the cells to Number with one decimal place it's OK for the
> > > decimals but I would like it to just show 20 (not 20.0) is there a way to do
> > > this?
> > >
> > > (I've tried formatting the cells to Text and General - these both round Up
> > > the .5
> > > I've formated the sourse of the Drop-down list as TEXT).
> > > The TOTALS column on this sheet is fomatted to General & this displays as
> > > wanted.
> > > The TOTALS are e.g. =SUM(B4:T4).
> > >
> > > Sheet 2 of this book is layed out the same as Sheet 1 and the values entered
> > > into sheet 1 are copied to sheet 2 using the Absolute Cell Ref $C$R. This
> > > sheet gets sorted by TOTAL so this sheet is an Update sheet.
> > > I'm having the same problem with this sheet as given above.
> > > Will the Fix for Sheet 1 also fix Sheet 2 or will I need something else?
> > >
> > > As another question, how can I get Sheet 2 to NOT show 0 when cells in Sheet
> > > 1 are empty? (I've got this sheet formatterd as Number right now and each
> > > cell has ='SHEET 1'!$C$R reffering to it's corresponding cell on Sheet 1).
> > >
> > > Thanks for any assistance
> > > John
> >
> > --
> >
> > Dave Peterson
> > .
> >

--

Dave Peterson