Zero cell value until manual input

diatex

Board Regular
Joined
Jan 16, 2004
Messages
96
Good evening or what ever the time of day is for you

I need a formula that will fill certain cells with a Zero value until i manually insert an higher value.

The range of these cells to be filled are 5 rows apart e.g.
A3:A9, F3:F9 etc. The cells in between must be empty. Copy and paste is not a problem if it is easier to fill one range at a time

Any help is some help

Diatex

I can use Edit/GoTo/Special/Blanks-OK. Then type 0 and press Ctrl+Enter but i thought a formula would be easier.
 

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

BrianB

Well-known Member
Joined
Feb 17, 2003
Messages
8,127
If you just want a zero value cell to show zero you can use a cell format that has a zero after the second sem-colon, like :-
#,##0.00;-#,##0.00;0
 

diatex

Board Regular
Joined
Jan 16, 2004
Messages
96
Thanks BrianB for your reply, what i was hoping for was to designate certain cells that would automatically show Zero without having to copy paste etc. Formating wasn't really an issue as my zero value could be replaced with a three digit number at a later date.
The idea behind the Zero value in the cell prior to a greater value being entered was to enable my range to calculate rather than return a #NUM!error.
I was beginning to think that i had been given the cold shoulder by the board, but at least you took the time to reply and for that i must thank you again.


Diatex
 

Watch MrExcel Video

Forum statistics

Threads
1,122,193
Messages
5,594,767
Members
413,933
Latest member
Msar5586

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top