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.
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.
