leve chart

[Fareez]

Dear Friends
I want to prepare leve chart . plese help me to solve it
Eg
A B C D
1 Fareez H N
2 Pathma
3 Rusdhi N H
I have entered staff name in cloumn and date in row. in this list Fareez is
back up for Rusthi. if rusthi takes leave , Fareez can not be allowed for
taking leave.

I am using " H" for annual Leave "N" means not allowed to take leave. if I
enter H for rusthi then autoimaticaly N sld come for Fareez and vice versa.
I used "If "function but not much support. please note that if I enter H for
Rusthi and If leave is cancelled later I sld be able to delete H without any
affect to formula that cell contains .
there is no must to use N if we can block that cell also fine

Please help me

 

[Shane Devenshire]

Hi,

You can not do this with a spreadsheet formula because you it would be a
circular reference. Your only alternative would be to use a VBA macro.
However, we would need to know how Pathma fits into the picture. How do we
know who is the backup for who?

 

[Ron@Buy]

Perhaps an option would be to not use an N and create a Conditional Format to
change the cell colour maybe to Red !?

 

[Fareez]

As you said can use conditional Foramat too. and pathma not back up for any
one assume

 

[Ron@Buy]

In cell B1 enter Conditional Format =B3="H" and select colour, of your
choice, to format. In cell B3 enter Conditional Format =B1="H" and select
same colour.
Now when you enter H against either of the names the corresponding cell
against the other person will change colour indicating "can not be allowed
for taking leave."
Hope this helps

 

[Fareez]

Thanks. could you please further explain how i can enter this conditonal
format if you dont mind.