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.

Say if your names are in column A and dates when they are going to be on
leave in column B
then using the following formula will put the employee names in 4 separate
column begininning column D thru column G (for 4 separate quarters)
On cell D1 type the following
=INDEX($A$1:$A$8,SMALL(IF((INT((MONTH(($B$1:$B$8)) +2)/3))=COLUMN()-3,ROW($B$1:$B$8)),ROW(1:1)))
Array enter it (ctrl+shift+enter)
Then copy and paste it all the way down column d and across thru column G





"Col" wrote in message
...
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.

Col wrote...
....
An example formula is;

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

This formula can only show one name. It looks like you're missing right
parentheses. I believe you want

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

One question: all the other names correspond to a progression from
Main!I2 through Main!O2 except John, which refers to Main!F2 rather
than Main!M2. Is that correct?

One question: all the other names correspond to a progression from
Main!I2 through Main!O2 except John, which refers to Main!F2 rather
than Main!M2. Is that correct?


Yes, well spotted but it is correct.

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