Hi Bob,

Thanks for the interest in helping,

most probably, the logic behind the simple holiday planner is known right?
wherein we have column a, b, c as source for the sumproduct function.
a range = SNAMES, b range = SFROM, c range = STO.

but my problem is i want to have another column D for whereabouts like what
is the reason for the holiday, is it Vacation leave, illness etc.

so i want to plot the dates based on NAME and whereabout...

the output should somehow be like this but i want it to automatically plot
the dates in the calendar
INPUT DATA:
A B C D
Snames Sfrom Sto Swhere
Mikel Jan 1 Jan 4 Illness

OUTPUT:

January
1 2 3 4
Mikel ILL ILL ILL ILL
etc.

since sumproduct displays output as number, i have a custom if function
wherein if the output of sumprod is equal to lets say 4 it will display ILL
etc.,
that is the reason why multiply my formula to different number to have
different output based on whereabouts.

Hope you get mine.

and thanks in advance for your help

"Bob Phillips" wrote:

Mikel,

If you explained the business logic behind this, I am sure we could do
better.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"mikel" wrote in message
...
Hi,

I have a worksheet called holiday planner,
i use sumproduct for plotting the dates.
however i need to have another column for whereabouts.
also i use a custom if function to determine the whereabout since
sumproduct
returns number.
my formula goes like this
SN = array for name
SW= array for whereabouts
SF=array for FROM
ST=array for TO

=((SUMPRODUCT(($G11=sn)*(sw="CRA")*(sf<=H$7)*(st= H$7)))+0.1)*((SUMPRODUCT(($G11=sn)*(sw="MNL")*(sf< =H$7)*(st=H$7)))+0.2)*((SUMPRODUCT(($G11=sn)*(sw= "PSL")*(sf<=H$7)*(st=H$7)))+0.3)*((SUMPRODUCT(($G 11=sn)*(sw="RTM")*(sf<=H$7)*(st=H$7)))+0.4)*((SUM PRODUCT(($G11=sn)*(sw="VLA")*(sf<=H$7)*(st=H$7))) +.5)*((SUMPRODUCT(($G11=sn)*(sw="WAL")*(sf<=H$7)*( st=H$7)))+.6)*((SUMPRODUCT(($G11=sn)*(sw="HAM")*( sf<=H$7)*(st=H$7)))+.7)*((SUMPRODUCT(($G11=sn)*(s w="BUD")*(sf<=H$7)*(st=H$7)))+.8)*((SUMPRODUCT(($ G11=sn)*(sw="TOI")*(sf<=H$7)*(st=H$7)))+.9)*((SUM PRODUCT(($G11=sn)*(sw="HOL")*(sf<=H$7)*(st=H$7))) +1)*((SUMPRODUCT(($G11=sn)*(sw="ILL")*(sf<=H$7)*(s t=H$7)))+2)*((SUMPRODUCT(($G11=sn)*(sw="NIT")*(sf <=H$7)*(st=H$7)))+3)*((SUMPRODUCT(($G11=sn)*(sw=" PH")*(sf<=H$7)*(st=H$7)))+4)*((SUMPRODUCT(($G11=s n)*(sw="TC")*(sf<=H$7)*(st=H$7)))+5)*((SUMPRODUCT (($G11=sn)*(sw="TE")*(sf<=H$7)*(st=H$7)))+6)*((SU MPRODUCT(($G11=sn)*(sw="WOO")*(sf<=H$7)*(st=H$7)) )+7)*((SUMPRODUCT(($G11=sn)*(sw="WTF")*(sf<=H$7)*( st=H$7)))+8)

this works okey for me, however, as i progress in making my holiday
planner,
since the rows grows larger, it gives me an error FORMULA TOO LONG.

Can anyone help me?

Thanks in advance