Modify Sumproduct to include helper column

Hi all,

I'm using Excel 2007 and have an Employee Scheduling Program that keeps track of 10 employees on a monthly basis (1 worksheet per month). The days of each month are in columns (I thru AM) and my 10 employees are in Rows 6 thru 15, which creates a grid of cells. I use Conditional Formatting to highlight the Weekends, Todays Date, and Holidays. My Sumproduct formula (shown below) is in each of the cells of my grid and places a number (1 to 10 for each employee) from start date to the end date. My Current formula works great as it finds every occurrence of the argument but I need to modify it to include the contents of the Helper Column.

If(Sumproduct(($g$44:$g$74=$c$6)*($m$44:$m$74<=i$4)*($t$44:$t$74>=i$4)),1,0).

where,
Row 4 (I to AM) = 1 to 31 (days in December)
Column C (6 to 15) = Numbers 1 to 10
Column G (44-74) = Numbers from 1 to 10
Column M (44-74) = Start Dates of a Project
Column T (44-74) = End Dates of a Project
Column A (44-74) = Helper Column (ex: finds the 1st No. 1 and puts 11 in the same row column A cell, finds the next No. 1 and puts 12 in the cell, etc.)

So, the above formula (in cell I$6 copied across) looks in Column G for the number 1, finds the corresponding Start & End Dates for every occurrence and puts the number 1 in each cell of Row 6 (I6 to AM6). The formula in Row 7 compares Column G to $c$7 and if true, places the number 2 in each cell, and so on to Row 15.

If the Sumproduct is True, my goal is to replace the Numbers (1 to 10) in my formula with a statement that would input the contents of the Helper Column (i.e., 11, 12, 31, 42, whatever) instead of having just the No. 1 in Row 6, just the No. 2 in Row 7, etc. I've tried a Vlookup (shown below) but it only finds the 1st occurrence (i.e, 11, 21, 31, 41, etc.)

If(Sumproduct(($g$44:$g$74=$c$6)*($m$44:$m$74<=i$4)*($t$44:$t$74>=i$4)),VLOOKUP($C$6&COLUMN($A$44:$A74),$A$44:$T$74,1,0),0).

A copy of the worksheet can be found on an earlier post dated 12-11-2007 entitled, Conditional Format a string of cells that begin before today & end after today.

Any help would be greatly appreciated!

Jim

Maybe you can re-post your sample workbook and show examples of expected results and how they should be obtained?

Try this formula in I6, then copied down and over

Please Login or Register  to view this content.

is that what you need?

Oh, I see now what you're looking for...

try this formula in I6:

Please Login or Register  to view this content.

this formula must be confirmed with CTRL+SHIFT+ENTER, not just ENTER... you will see curly { } brackets appear, if entered correctly.

Then copy the formula over and down.

Now I'm going to try to figure out a Conditional Format to Change cells to Yellow if Cell >= 1 and Today > Start Date but < End Date.

How about for I6 yellow Conditional Formatting:

Please Login or Register  to view this content.

Yeah, it probably is an ordering thing...

Conditional Formatting works like this...

If the first condition is satisfied, it will apply that format and stop...

If the first condition is not satisfied, it will go to the next condition and so on...

so you must be careful of the ordering... if more than one condition applies only the first one that it comes across will be used.

You are very welcome!