Hi all,

Hope some of you real experts can help with this one!

Trying to put together an Excel file which contains a master tab with staff
names on (called Main) which also contains holiday dates, another sheet is
designed around a year planner but split into four quarters which I would
then like to automatically put the staff name in a column headed by the
financial weeks of the year.

I'm trying to use the IF AND function and I've got it working fine except
that only one name is displayed at once, when I try to concatenate by using
'&' it doesn't work, however I've tried just using the IF function on its
own and that works fine too, only problem is I need to use the AND function
too (as I see it) and this is where I'm stuck.

An example formula is;

=IF(AND($L$2-1=Main!I2,Main!I2=$L$2-7),"Anne",""&IF(AND($L$2-1=Main!J2,Ma
in!J2=$L$2-7),"Phil",""&IF(AND($L$2-1=Main!K2,Main!K2=$L$2-7),"Gary",""&I
F(AND($L$2-1=Main!L2,Main!L2=$L$2-7),"Sandra",""&IF(AND($L$2-1=Main!F2,Ma
in!F2=$L$2-7),"John",""&IF(AND($L$2-1=Main!N2,Main!N2=$L$2-7),"Alec",""&I
F(AND($L$2-1=Main!O2,Main!O2=$L$2-7),"Louise"," ")))))))

....and is in cell K3, in the formula above L2 is the commencing date of the
following week with the cell references from the Main tab referring to the
staff members.

If two members of staff are taking the same leave week then only one name is
displayed.

Any ideas?

Any help and advice much appreciated.

Regards,

Colin.


--
Remove the 'old' to reply to me.