• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

SUMPRODUCT in VBA

Lucoz

New Member
Hey guys. Found this site last week and love it. It helped me with a query I'd been working on for days.


Now, however, my query has increased into something I can't sort and was hoping for some assistance.


Basically, I found Chandoo's SUMPRODUCT formula (SUMPRODUCT(--(B6:B15<=C3)*(C6:C15>=C3),ROW(B6:B15))-5) and used it. It worked beautifully. Now, I need it to do something else and I have no idea how to do it, or even if it's possible.


I have a workbook containing about 20 worksheets that are identical in structure (that people use to record 3 different types of annual leave (Standard, Flexible and special) in 3 separate groups, and a summary sheet that I'd like to amalgamate all of the previous sheets onto. The staff worksheets have 3 sections where annual leave can be recorded, and in each section it is recorded using a start date and end date. The number of days required is caluclated automatically.


The summary sheet consists of all workdays for the year (excluding weekends and bank holidays) across the top row (A) and the names of staff down column 1 (starting at row 2). A bit like this:


01/01/2011 02/01/2011 03/01/2011

Steve

Jenny

Frank


Using the above mentioned SUMPRODUCT formula, adding it to an IF statement, and entering it into a cell, I can now get the summary sheet to enter 'ST' into the relevant cell if the date above falls between dates given for Standard annual leave. I.e. if Steve requests Standard annual leave from 01/01/2011 to 03/01/2011, the initials ST would appear beneath 01/01/2011, 02/01/2011 and 03/01/2011.


This is all well and good, however I need different things to appear in the relevant cells depending on whether the date on the summary sheet falls between dates given in the Standard, Flexible or Special leave sections. I.e. if, on her annual leave worksheet, Jenny requests Standard leave on 01/01/2011 then requests Flexible leave on 02/01/2011 and 03/01/2011 the initials 'ST' should appear on Jenny's line beneath 01/01/2011 and 'FL' should appear beneath 02/01/2011 and 03/01/2011.


I've tried a long, convoluted IF statement to the effect that if the sumproduct formula equals 0 when checking one set of data, to move onto the next etc, but I can only get it to work for 2 sets of data; add a third and the first set stops producing results (no idea why). However, even if I did manage to work it out, the formula is horribly cumbersome and messy.


Could what I want be done using VBA?


Hopefully I've explained it properly.
 
Yes. I'm sure it can. I'm not even seeing anything here that guarantees the need of VBA.


Can you provide a mock up of your data and show us what you want to do?
 
Awesome. The spreadsheet is here:


https://spreadsheets.google.com/ccc?key=0AuNGIfcbtpZxdFR0U3BvR0tzSC1PVFN4emFGZWRWdkE&hl=en&authkey=COvUqPgL


If you look at the 3 staff sheets, you will see that they are identical and that there are 3 sections for leave to be entered, using a start date and end date (the number of days are calculated automatically).


What I would like is whenever someone enters leave on their sheet in one of the 3 sections, I would like their line on the calendar sheet to automatically enter the relevant code (either AL, SL, or FL) in the required dates depending on whether the dates at the top of the calendar sheet fall between the dates specified.


You can see on the example that I have entered annual leave dates for Mike as 01/04/2011 and 07/04/2011 giving him 7 days leave. This, in turn, should enter AL in Mike's row under those two dates and all dates between them (as per the example). Likewise his SL on 11/04/2011 and 12/04/2011 should enter SL under those two dates (and any that fall between).


Does that make sense?


The reason I thought VBA might work better is that I can almost guarantee that as soon as I get in working someone will ask if it's possible to enter sickness (or some other parameter) on the calendar sheet as well and I wanted to allow space for this should it occur (always the pessimist).
 
Doing the latest event for anyone will be doable

Doing all of a leave type may require VBA

I'm assuming you want to do all events?
 
Well, the system has been setup so that only one person can access it at a time (something to do with limiting access and reducing something or other), so it would only need to sort a single persons information a time. Of course, it would need to sort all of that persons leave from the 3 sections if they update every section.


I'm just totally stuck. I tried a horribly messy (and incredibly long) IF/SUMPRODUCT formula that, for some reason, stopped calculating the first figure when I added a third criteria (i.e. I could calculate 2 sets of leave brilliantly, but as soon as I added the third set the first calculation stopped working and always returned 0). I also tried an even messier system with extra 'helper/calculation' rows that each calculated one set of leave. I then used the original calendar rows to amalgamate these answers. This worked but, obviously, given the size of the data set, slowed the system down almost entirely.


I eventually admitted efeat and tried here.
 
OK, this is horribly in-elegant (bordering on the horrific), but it should do the above mentioned task I need (it'll add row numbers in, but I can work around that with conditional formatting), I just can't get it to work correctly. Is anyone able to explain what's up with it (aside from the fact that it's just too damn ugly).


It is a single formula that I have separated into individual IF statements for easier viewing. The INDIRECT function is for generating sheet references.


If I only enter the first 2 IF statements it works fine and will return the correct figures for 2 sets of annual leave. Yet when I add the third section in, the first 2 sections still work, but it won't return figures for the 3rd and remains at 0.


Any ideas (and no laughing at it)


=IF(SUMPRODUCT(--(INDIRECT("'"&$A3&"'!"&"A20:A45")<=B2)*(INDIRECT("'"&$A3&"'!"&"B20:B45")>=B2),ROW($A$20:$A$45))>0,

SUMPRODUCT(--(INDIRECT("'"&$A3&"'!"&"A20:A45")<=B2)*(INDIRECT("'"&$A3&"'!"&"B20:B45")>=B2),ROW($A$20:$A$45)),


IF(SUMPRODUCT(--(INDIRECT("'"&$A3&"'!"&"A20:A45")<=B2)*(INDIRECT("'"&$A3&"'!"&"B20:B45")>=B2),ROW($A$20:$A$45))=0,

SUMPRODUCT(--(INDIRECT("'"&$A3&"'!"&"M5:M20")<=B2)*(INDIRECT("'"&$A3&"'!"&"N5:N20")>=B2),ROW($A$4:$A$19)),


IF(SUMPRODUCT(--(INDIRECT("'"&$A3&"'!"&"M5:M20")<=B2)*(INDIRECT("'"&$A3&"'!"&"N5:N20")>=B2),ROW($A$4:$A$19))=0,

SUMPRODUCT(--(INDIRECT("'"&$A3&"'!"&"M26:M45")<=B2)*(INDIRECT("'"&$A3&"'!"&"N26:N45")>=B2),ROW($A$46:$A$66)))))
 
Back
Top