# How to keep cell blank until data is entered in another cell

I want to leave the columns B and H blank, until data is entered in column D. How do i do that?

The formula in column H is =B-D

I'm using using Excel on Mac. ``````B4 - Total number of hours in bank
D4 - Number of hours used
F4 - Date
H4 - Remaining hours
``````
• If B and H (and also D) columns are blank initially, from where does the 8 hour jump to column B, when you enter something in D? Jun 17 '18 at 19:40
• You would need to tell us how u get the value for Column B for us to help you.
– user912964
Jun 17 '18 at 19:56
• From where you are getting the value in column B ? Jun 18 '18 at 7:31

Assuming Column B is a static value of 6.5 or your data comes from another cell/sheet/workbook/formula.

# ISBLANK Method

In Column B

Since I do not know where your values for column B are coming from below are several options.

• If your values in Column B are entered in manually try,

``````=IF(ISBLANK(\$D5),"",8)
``````
• or if your values is remaining hours from Column H calculations,

``````=IF(ISBLANK(\$D5),"",\$H5)
``````
• or from another cell,

``````=IF(ISBLANK(\$D5),"",Q5)
``````
• or even if from another sheet,

``````=IF(ISBLANK(\$D5),"",Sheet2!\$B5)
``````
• or from another workbook,

``````=IF(ISBLANK(\$D5),"",'C:\TimeBank\[NameofSpreadsheet.xlsx]Sheet1'!\$A5)
``````

In Column H

``````=IF(ISBLANK(\$D5),"",\$B5-\$D5)
`````` • @tom-brunberg using simple algebra mate, column H6 is 6.5 with column D6 being empty. Therefore =B6-D6 being 6.5-0 means B6 is a static 6.5 and with row 5 in OP's post showing D5 as 1.5 and B5 resulting in 8 therefore B5+D5 is 6.5+1.5. Jun 18 '18 at 4:56
• I added a translation of the column headers to the question. Do you still think your interpretation is correct? Jun 18 '18 at 5:09
• Ohh thank you very much for the translation my Norwegian isn't too spot on, I've submitted a revert to my edit of OP's question and updated my answer to include an alternative. Jun 18 '18 at 6:04
• @angelofdev, but your formula in B will only insert 6.5, how the formula will handle if any other value is required, like in OPs post check the first value is 8.00 Jun 18 '18 at 7:30
• @angelofdev I'm not a fan of using colors to hide text. The number format `;;;` is a much better option, IMHO, since it's color agnostic.
– piko
Jun 20 '18 at 6:36

You never clarified what data/formula you want to show in column B, so I will simply hardcode the initial 8 hours. To answer your question: How do I leave columns B and H blank, until data is entered in column D ?, enter the following formula in B5:

``````=IF(D5="","",8) 'or instead of the hardcoded 8, a reference to whatever source there are for the 8.0 hours)
``````

and in cell B6:

``````=IF(D6="","",H5)
``````

and copy down to B29

Note, that it is intentional, that cell B6 and down, fetches the display value from the cell in column H and previous row, where the remaining hours were calculated.

Then for the H column (remaining hours) add the following formula in H5

``````=IF(D5="","",B5-D5)
``````

and copy down to H29

Image after 1 hour entered in D7 To execute the entire scenario successfully, you need Helper Column like I've created in Column A. Since Hours used in Bank is mandatory. And it should never a fixed value.

Check the Screen Shot: • First fill the helper column with values.
• Write this formula in B2: `=IF(\$D2="","",\$A2)` & fill it down.
• Enter this in H2: `=IF(\$D2="","",\$B2-\$D2)`and fill it down.
• Finally start filling cells in column D. You get desire result.

This may not be strictly what you were asking for, but hopefully it's better.

The result just after Tab has been pressed after typing `2.5` in the `Hours used:` column can be seen in this screenshot: The worksheet is to be set up as follows:

Enter `8` in `B5` (or use whatever formula you do to calculate the value)

Enter the following formula in `B6` and ctrl-enter/copy-paste/fill-down the rest of the column as required:

``````=H5
``````

Enter the following formula in column `H` where appropriate:

``````=IF(ISBLANK(D:D),"",B:B-D:D)
``````

Note that an entire column reference can be used instead of the specific cell reference to refer to cells in the same row as the formula itself. (The "normal' formula would, of course, be `=IF(ISBLANK(D5),"",B5-D5)` entered in `H5`.)

As you can see, this enhanced solution pre-fills the next cell of the `Banked hours:` column with the amount of remaining hours.

In fact, the whole table can be improved by removing the `Remaining hours` column entirely: Enter the following formula in `B6` and ctrl-enter/copy-paste/fill-down the rest of the column as required:

``````=IF(ISBLANK(D5),"",B5-D5)
``````

This formula refers to the cells in the previous row rather than the current one.

Notes:

• `ISBLANK(D5)` is equivalent to `D5=""` provided `D5` doesn't contain a formula. For the case of a formula, only `=""` works correctly. `ISBLANK()` will always return `FALSE` even if the formula evaluates to `""` thus showing a "blank" cell.