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 my strong point I'm afraid but I'm sure there are others on the site who may be able to help you.

Ed