20 reasons is no big deal. Put them in a lookup table, one piece of code.

Why do you want VBA, it won't be efficient.

I still do not understand why you original formula is multiplying all of
those SUMPRODUCTS together, understanding that is the key to giving you a
decent solution. Were you trying to weight each absence type?

--
HTH

Bob

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

"mikel" wrote in message
...
i don't think it would be possible because i have a large list of names,
and
i think 20 reason to be on holiday.

could you help me translate this to vba so that i can use this on my
formula

my simple equation is :

=SUMPRODUCT(($G11=sn)*(sw="CRA")*(sf<=H$7)*(st=H$ 7))

$G11 and H$7 are variables
SN,SW,SF,ST are ranges

a vba function will be great.

thanks
"Bob Phillips" wrote:



"mikel" wrote in message
...
Hi Bob,

Thanks for the interest in helping,

most probably, the logic behind the simple holiday planner is known
right?

Not here!


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

If I understand, and that is a big if, you want to extract from a table
the
number of days of illness and then plot this in another table?

So what happens they are Ill between 1/4 Jan, and on vacation between
10/15.

The way i do this sort of thing is to have a gantt type table, name in
column A, start date in B, end date in C, reasoon in D. In E on in row 1
I
have dates. Then I check in E2 etc like so

=IF(AND($B2<=E$1,$C2=E$1),VLOOKUP($D2,{"Illness", "I";"Holiday","H"},2,FALSE),"")

and probably use conditional formatting to colour the cells for visual
effect.