Clearing Cells

[Guest]

Hi
I'm using an Excel sheet for pricing items customers purchase.

In A2 I could enter any of 4 prices e.g. £30 , £15, £10, £5
In B2, C2, D2, E2, I have entered the following formula =IF(A2=Rate_30,A2,0)
It follows that B2 shows £30 and C2,D2,E2 show 0.00 that works ok the
problem is with 200 rows its a lot of zero's is it possible to add to the
formula something that clears all the zero's so that the columns are easier
to read. This sheet is used on a daily basis so the customer could call in
tomorrow and buy something at £15 the £30 would clear and show in the C2 cell
with all the others clear.

Any help would be much appreciated

Mully

 

[Guest]

Hi
I'm using an Excel sheet for pricing items customers purchase.

In A2 I could enter any of 4 prices e.g. £30 , £15, £10, £5
In B2, C2, D2, E2, I have entered the following formula =IF(A2=Rate_30,A2,0)
It follows that B2 shows £30 and C2,D2,E2 show 0.00 that works ok the
problem is with 200 rows its a lot of zero's is it possible to add to the
formula something that clears all the zero's so that the columns are easier
to read. This sheet is used on a daily basis so the customer could call in
tomorrow and buy something at £15 the £30 would clear and show in the C2 cell
with all the others clear.

Any help would be much appreciated

Mully

Try clearing the zero values checkbox, Tools>Options>Zero Values

 

[Guest]

Replace your 0 with ""
This is a null so it will not display unless A2=Rate
Pat

 

[Guest]

Hi to All

Thanks for the info both worked and did the job however used the "" it
appeared to suit my set up better.
Another question is it possible to hide or lock the formula so members of
staff don't mess with it or delete it by accident - often have to Sort the
sheet during business hours so have tried sheet protection and locking and
hiding this is a bit cumbersome is there anything easier and quicker.

Once again thanks for your help

Cheers

Mully