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

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?

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Fareez" wrote:

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

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


Shane Devenshire" wrote:

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?

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Fareez" wrote:

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

As you said can use conditional Foramat too. and pathma not back up for any
one assume
"Ron@Buy" wrote:

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


Shane Devenshire" wrote:

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?

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Fareez" wrote:

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

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


As you said can use conditional Foramat too. and pathma not back up for any
one assume
"Ron@Buy" wrote:

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


Shane Devenshire" wrote:

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?

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Fareez" wrote:

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

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

"Ron@Buy" wrote:

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


As you said can use conditional Foramat too. and pathma not back up for any
one assume
"Ron@Buy" wrote:

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


Shane Devenshire" wrote:

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?

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Fareez" wrote:

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