nest multiple IF and AND functions in one cell.

[djones5]

Hello,

I'm actually going crazy - I need to perform the follow and I can't seem to get it to work - any kind helpers out there??

So--

I'm trying to create an automated rota where the reception lead can select from drop boxes when a member of staff starts, leaves, at which location and whether the time she is entering is in fact annual leave, sick leave or other leave.

I then want this information to transport to a head sheet to produce numbers so that I can conditional format it to change the box colour. Hopefully that makes sense.

In words, I want the forumula to be able to recognise if, at say 7.00AM, if the start drop down box has 7.00AM selected in it it should give me a 1 (which will then go green) but if the finish box has 7.00AM in it it should give me a 2 (which will then go red).

However, if either of those boxes are selected and one of the following additional options are selected, I want it to give me the number associated with that choice.

- Annual Leave - 3 - to make colour go red
- Sick Leave - 4 - to make colour go yellow
- Other Leave - 5 - to make colour go blue

Therefore (if A1 = Start, A2 = Finish, B1 = Annual Leave, C1 = Sick Leave, D1 = Other Leave)

IF (A1 = 7.00 AND B1 = Yes) is true give me a 3, if false give me a 1
IF (A1 = 7.00 AND C1 = Yes) is true give me a 4, if false give me a 1
IF (A1 = 7.00 AND D1 = Yes) is true give me a 5, if false give me a 1
IF A1 doesn't = 7.00 give me a 0
IF (A2 = 7.00 AND B1 = Yes) is true give me a 3, if false give me a 1
IF (A2 = 7.00 AND C1 = Yes) is true give me a 4, if false give me a 1
IF (A2 = 7.00 AND D1 = Yes) is true give me a 5, if false give me a 1
IF A2 doesn't = 7.00 give me a 0

I think I was nearly there but can't seem to make it work - any help would be officially amazing.

Thanks in advance,

Darren

[sweep]

Hi,

Does this give you a start?

Please Login or Register  to view this content.