0

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.

image of the Excel sheet

Translation of column headers:

B4 - Total number of hours in bank
D4 - Number of hours used
F4 - Date
H4 - Remaining hours
Improve this question
3
  • 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?
    – Tom Brunberg
    Jun 17, 2018 at 19:40
  • 1
    You would need to tell us how u get the value for Column B for us to help you.
    – user912964
    Jun 17, 2018 at 19:56
  • From where you are getting the value in column B ?
    – Rajesh Sinha
    Jun 18, 2018 at 7:31

4 Answers 4

Reset to default
4

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)

enter image description here

Improve this answer
5
  • @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.
    – angelofdev
    Jun 18, 2018 at 4:56
  • I added a translation of the column headers to the question. Do you still think your interpretation is correct?
    – Tom Brunberg
    Jun 18, 2018 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.
    – angelofdev
    Jun 18, 2018 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
    – Rajesh Sinha
    Jun 18, 2018 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, 2018 at 6:36
1

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

enter image description here

Improve this answer
0

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:

enter image description here

  • 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.
Improve this answer
0

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:

Worksheet Version 1 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:

Worksheet Version 2 Screenshot

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.
Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .