Taking letters away from numbers!

[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

[Dave Peterson]

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.

tox wrote:
>
> 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

--

Dave Peterson

[Max]

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
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"tox" <nospam@nospamming.co.com> wrote in message
news:kmbOd.3476$OI2.2291@newsfe1-gui.ntli.net...
> 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

[Ken Wright]

=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.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"tox" <nospam@nospamming.co.com> wrote in message
news:kmbOd.3476$OI2.2291@newsfe1-gui.ntli.net...
> 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
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>