Taking letters away from numbers!

  • Thread starter Thread starter tox
  • Start date Start date
T

tox

OK, I have a column and in the top cell of the column I have the number of
days holiday an employee is entitled to. Now, every time I enter, say the
letter H in the column for each days holiday taken, I want it to
automatically deduct a day from the number in the first cell of the column.
In other words, if I have 28 in the first cell of the column, and in the
next five cells I have the letter H, I wish to see the first cell showing 23
days.

Also, is it possible get the H to turn a different colour, say, red
automatically?

TIA
tox
 
One way

Assume the target range where the "H"'s will be entered is B2: B30

Put in B1: =28 - COUNTIF(B2:B30,"H")

Select B2:B30

Click Format > Conditional Formatting (CF)

Under Condition 1, make the settings as:
Formula Is| =B2="H"
Click Format button > Font tab > Red & bold? > OK
Click OK at the main dialog

B1 will return the residual number depending on the # of "H"'s indicated in
B2:B30, and the CF will format the font as you wished

Adapt to suit
 
How about using a few cells.

Put 28 in H1
put this in H2:
=countif(H4:H1000,"H")
(adjust the range to match your data)
put this in H3:
=h1-h2

And take a look at Format|conditional Formatting.

You can use that to change colors based on a value in a cell.
 
=28-COUNTIF(A2:A30,"H")

But, poor spreadsheet design to hardcode variables into your formulas.
Assume all employees in the file get the same holiday, Define a name such as
'Hols' as the value 28. Then use the name in your formulas

=Hols-COUNTIF(A2:A30,"H")

Then if the 28 changes you simply change the value associated with Hols and
all formulas update. Or stick the 28 in a cell somewhere, name the cell and
then use the name.
 
Back
Top