Conditional Formatting help.

[mikel301292]

Im creating a planner for my local GP which, ive got a small problem
with. Basically on it its got, the ammount of appointments each Gp has,
and above it has whether they are "in" on that day or "off". But the
Problem is that when someone is on holiday, a "h" goes into the box, i
want this then to turn the box with the ammount of appointments in to
"0".
I've tried using the basic "IF" fuction: =IF(A1="H",0), which makes
the box go to 0 when there is a h but i cant type any other number into
that cell because it will over ride the forumlae in the cell.
I then tried to type the same forumlea into the conditional
formatting section but that just does nothing.
If you have any way of solving this problem please help me. Thanks

 

[EdMac]

Mike,

One way to do this is through Data validation (DATA > Validation)


In the cell where you want the numbers or 0 to be go to data
validation

Select list then in the box put =IF(Cell_that_has_H="h",0,1,2,3,4,etc)

This will only allow 0 to be entered into the cell when H is selected,
make the list of numbers as long as you like/need or you could use a
named range.

Not all that pretty but it works.

Ed

 

[mikel301292]

Thanks for your help Ed, but The problem isnt with the manual entering
of the number, i need it to automatically change when "h" is entered
into the cell.

 

[EdMac]

Mike,

In that case, I think the only solution would be using a macro - not m
strong point I'm afraid but I'm sure there are others on the site wh
may be able to help you.

E