Crazy Hard Function

[Chris]

Ok, so say on Sheet20, you have a calendar, and below each date listed is a
blank.

Jan 09
1 2 3 4 5 6 7
* * * * * * *
8 9 10 11 12 13 14
* * * * * * *
15 16 17 18 19 20 21
* * * * * * *
22 23 24 25 26 27 28
* * * * * * *
29 30 31
* * *

On Sheet18, you would see the following, just many many more rows:

150 55555-01 1 1 1 0 0 0 0 3 0 0 1/10
101 55555-03 1 1 0 0 1 0 0 1 0 0 5/15
102 55555-04 1 1 1 0 1 0 0 4 0 1 4/23
101 55555-01 0 0 0 0 0 0 1 0 0 0 3/10

On Sheet1 you have multipliers, or points, that each item for sale is worth.
So if item 1 was worth 1 point, you'd get 1 point each time you sold it.
There are 10 items with points that vary depending on the item. The
multipliers on Sheet1 are located in cells H1-H10. On Sheet18, you only have
the quantity for each item sold in its particular column.

What I want is for the "*" on the calendar to be the sum of all rows' point
values in Sheet18 where the date matches above it, so that someone could
easily look at this calendar and say, "I made 5 points on this day, and 23 on
this other day"

 

[Chris]

I think I may have explained it better finally in one of my last couple of
posts... The first post I did seemed to only confuse... How about we ignore
my first post up there, haha.

 

[Chris]

I think I may have explained it better finally in one of my last couple of
posts... The first post I did seemed to only confuse... How about we ignore
my first post up there, haha.

 

[Max]

This modifies the earlier 2-step set-up suggested slightly to suit your
detail description/sheet structure that you have now provided.

In Sheet20,
Assume A1 contains the month-year's text string, eg: May 09
Assume A2:G2 contains the 1st row's "day" numbers: 1-7

Place in say A20:
=SUMPRODUCT((Sheet18!$M$2:$M$100=--(A$2&$A$1))*OFFSET(Sheet18!$C$2:$C$100,,ROWS($1:1)-1,))*Sheet1!$H1
Copy down 10 cells to A29, fill across to G29

Then place in A3: =SUM(A20:A29)
Copy across to G3 to return the required figs for each corresponding day
above in A2:G2

Repeat the construct likewise to cater for the other 4 "day" number rows in
the calendar. For neatness, minimize/hide away the working areas in row 20
down
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:370 Subscribers:68
xdemechanik

 

[Max]

This modifies the earlier 2-step set-up suggested slightly to suit your
detail description/sheet structure that you have now provided.

In Sheet20,
Assume A1 contains the month-year's text string, eg: May 09
Assume A2:G2 contains the 1st row's "day" numbers: 1-7

Place in say A20:
=SUMPRODUCT((Sheet18!$M$2:$M$100=--(A$2&$A$1))*OFFSET(Sheet18!$C$2:$C$100,,ROWS($1:1)-1,))*Sheet1!$H1
Copy down 10 cells to A29, fill across to G29

Then place in A3: =SUM(A20:A29)
Copy across to G3 to return the required figs for each corresponding day
above in A2:G2

Repeat the construct likewise to cater for the other 4 "day" number rows in
the calendar. For neatness, minimize/hide away the working areas in row 20
down
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:370 Subscribers:68
xdemechanik

 

[Rick Rothstein]

It is so much easier to develop solutions when you can "see" the actual
layout.<g> Okay, I believe the following User Defined Function (UDF) will
function as you want given the assumption that Sheet20!A1 contains a real
Excel date formatted to look like you showed and that the values in
Sheet18!M# (where # is a row number) are also real Excel dates as well....

Function TotalPoints() As Double
Dim X As Long, Z As Long, LastCell As Long
Dim S1 As Worksheet, S18 As Worksheet, S20 As Worksheet
Application.Volatile
Set S1 = Worksheets("Sheet1")
Set S18 = Worksheets("Sheet18")
Set S20 = Worksheets("Sheet20")
LastCell = S18.Cells(S18.Rows.Count, "M").End(xlUp).Row
For X = 1 To LastCell
If Month(S18.Cells(X, "M").Value) = Month(S20.Range("A1")) And _
Day(S18.Cells(X, "M").Value) = S20.Cells(Application.Caller. _
Row - 1, Application.Caller.Column) Then
For Z = 1 To 10
TotalPoints = TotalPoints + S1.Cells(Z, "H").Value * _
S18.Cells(X, 2 + Z).Value
Next
End If
Next
End Function

Again, to implement this UDF, press Alt+F11 from any worksheet to get into
the VB editor, then click Insert/Module from its menu bar and then
copy/paste the above function into the code window that opened up. Now, go
back to Sheet20 and put this formula in all the cells containing an asterisk
in the layout for Sheet20 that you posted to us...

=TotalPoints()

That's it... those cells should now show you the totals you are after.

 

[Rick Rothstein]

It is so much easier to develop solutions when you can "see" the actual
layout.<g> Okay, I believe the following User Defined Function (UDF) will
function as you want given the assumption that Sheet20!A1 contains a real
Excel date formatted to look like you showed and that the values in
Sheet18!M# (where # is a row number) are also real Excel dates as well....

Function TotalPoints() As Double
Dim X As Long, Z As Long, LastCell As Long
Dim S1 As Worksheet, S18 As Worksheet, S20 As Worksheet
Application.Volatile
Set S1 = Worksheets("Sheet1")
Set S18 = Worksheets("Sheet18")
Set S20 = Worksheets("Sheet20")
LastCell = S18.Cells(S18.Rows.Count, "M").End(xlUp).Row
For X = 1 To LastCell
If Month(S18.Cells(X, "M").Value) = Month(S20.Range("A1")) And _
Day(S18.Cells(X, "M").Value) = S20.Cells(Application.Caller. _
Row - 1, Application.Caller.Column) Then
For Z = 1 To 10
TotalPoints = TotalPoints + S1.Cells(Z, "H").Value * _
S18.Cells(X, 2 + Z).Value
Next
End If
Next
End Function

Again, to implement this UDF, press Alt+F11 from any worksheet to get into
the VB editor, then click Insert/Module from its menu bar and then
copy/paste the above function into the code window that opened up. Now, go
back to Sheet20 and put this formula in all the cells containing an asterisk
in the layout for Sheet20 that you posted to us...

=TotalPoints()

That's it... those cells should now show you the totals you are after.

 

[Harlan Grove]

Ok, so say on Sheet20, you have a calendar, and below each date listed is a
blank.  

Jan 09
1 2 3 4 5 6 7
* * * * * * *
8 9 10 11 12 13 14
* * * * * * *
15 16 17 18 19 20 21
* * * * *  * *
22 23 24 25 26 27 28
* * *  *  * *  *
29 30 31
* * *

I'll assume the Jan 09 is in cell Sheet20!A1 and is text, the 1 below
it is in cell Sheet20!A2, etc, so the whole thing spans Sheet20!
A1:G11.

On Sheet18, you would see the following, just many many more rows:

150 55555-01 1 1 1 0 0 0 0 3 0 0 1/10
101 55555-03 1 1 0 0 1 0 0 1 0 0  5/15
102 55555-04 1 1 1 0 1 0 0 4 0 1 4/23
101 55555-01 0 0 0 0 0 0 1 0 0 0 3/10

I'll assume there's a header row you're not showing, so this fragment
of the table spans Sheet18!A2:M5. I'll also assume the dates in column
M are date values formatted as m/d.

On Sheet1 you have multipliers, or points, that each item for sale is worth.
So if item 1 was worth 1 point, you'd get 1 point each time you sold it.
There are 10 items with points that vary depending on the item. The
multipliers on Sheet1 are located in cells H1-H10. On Sheet18, you only have
the quantity for each item sold in its particular column.

OK, point values are in Sheet1!H1:H10, so a vertical array in contrast
to the horizontal arrays of units sold for each item in the records in
Sheet18. This isn't an issue since you want the sums of the products
of unit counts times point values. The expression

MMULT(Sheet18!C2:L5,Sheet1!H1:H10)

returns an array of the point totals for each record in Sheet18.

What I want is for the "*" on the calendar to be the sum of all rows' point
values in Sheet18 where the date matches above it, so that someone could
easily look at this calendar and say, "I made 5 points on this day, and 23on
this other day"

Then the formula in Sheet20!C5 (1/10) could be the array formula

=SUMPRODUCT(--(Sheet18!$M$2:$M$5=--(C4&" "&$A$1)),
MMULT(Sheet18!$C$2:$L$5,Sheet1!$H$1:$H$10))

It'd be more efficient to add a column to the table in Sheet18 in the
first blank column to the right of the existing table, spanning the
same rows as the existing table, and containing the array formula

=MMULT(C2:L#,Sheet1!H1:H10)

where # would be the actual bottommost row in the table. I'll assume
this could go into column N. Then in Sheet20 you could use the simpler
formula

Sheet20!C5:
=SUMIF(Sheet18!$M$2:$M$#,--(C4&" "&$A$1),Sheet18!$N$2:$N$#)

Copy Sheet20!C5 and paste into the other cells in Sheet20 that need
similar formulas.

 

[Harlan Grove]

Ok, so say on Sheet20, you have a calendar, and below each date listed is a
blank.  

Jan 09
1 2 3 4 5 6 7
* * * * * * *
8 9 10 11 12 13 14
* * * * * * *
15 16 17 18 19 20 21
* * * * *  * *
22 23 24 25 26 27 28
* * *  *  * *  *
29 30 31
* * *

I'll assume the Jan 09 is in cell Sheet20!A1 and is text, the 1 below
it is in cell Sheet20!A2, etc, so the whole thing spans Sheet20!
A1:G11.

On Sheet18, you would see the following, just many many more rows:

150 55555-01 1 1 1 0 0 0 0 3 0 0 1/10
101 55555-03 1 1 0 0 1 0 0 1 0 0  5/15
102 55555-04 1 1 1 0 1 0 0 4 0 1 4/23
101 55555-01 0 0 0 0 0 0 1 0 0 0 3/10

I'll assume there's a header row you're not showing, so this fragment
of the table spans Sheet18!A2:M5. I'll also assume the dates in column
M are date values formatted as m/d.

On Sheet1 you have multipliers, or points, that each item for sale is worth.
So if item 1 was worth 1 point, you'd get 1 point each time you sold it.
There are 10 items with points that vary depending on the item. The
multipliers on Sheet1 are located in cells H1-H10. On Sheet18, you only have
the quantity for each item sold in its particular column.

OK, point values are in Sheet1!H1:H10, so a vertical array in contrast
to the horizontal arrays of units sold for each item in the records in
Sheet18. This isn't an issue since you want the sums of the products
of unit counts times point values. The expression

MMULT(Sheet18!C2:L5,Sheet1!H1:H10)

returns an array of the point totals for each record in Sheet18.

What I want is for the "*" on the calendar to be the sum of all rows' point
values in Sheet18 where the date matches above it, so that someone could
easily look at this calendar and say, "I made 5 points on this day, and 23on
this other day"

Then the formula in Sheet20!C5 (1/10) could be the array formula

=SUMPRODUCT(--(Sheet18!$M$2:$M$5=--(C4&" "&$A$1)),
MMULT(Sheet18!$C$2:$L$5,Sheet1!$H$1:$H$10))

It'd be more efficient to add a column to the table in Sheet18 in the
first blank column to the right of the existing table, spanning the
same rows as the existing table, and containing the array formula

=MMULT(C2:L#,Sheet1!H1:H10)

where # would be the actual bottommost row in the table. I'll assume
this could go into column N. Then in Sheet20 you could use the simpler
formula

Sheet20!C5:
=SUMIF(Sheet18!$M$2:$M$#,--(C4&" "&$A$1),Sheet18!$N$2:$N$#)

Copy Sheet20!C5 and paste into the other cells in Sheet20 that need
similar formulas.

 

[Chris]

Thanks for this, Rick. I think it's the right idea but I'm getting a "0"
returned on any date I throw in there.

If I can create a function and call it like this, I think if I could break
it down it would be easier for me to understand and correct any errors.

The first part is define what date we're matching. The date will always be
directly above where the function is used, and we're using that date as our
query for column "M" on sheet 18.

Second, I need to add the points for each row that matches and throw them
into a variable that will end up being the final result.

Then I need to know how to return the variable for final result.

If you can break how to do those things down, as I said, I should be able to
piece it together.

Again, thank you all very much.

 

[Chris]

Thanks for this, Rick. I think it's the right idea but I'm getting a "0"
returned on any date I throw in there.

If I can create a function and call it like this, I think if I could break
it down it would be easier for me to understand and correct any errors.

The first part is define what date we're matching. The date will always be
directly above where the function is used, and we're using that date as our
query for column "M" on sheet 18.

Second, I need to add the points for each row that matches and throw them
into a variable that will end up being the final result.

Then I need to know how to return the variable for final result.

If you can break how to do those things down, as I said, I should be able to
piece it together.

Again, thank you all very much.

 

[Chris]

I've added a column "O" that has each row's total point value already there.
So now all I need is for it to find all the rows on sheet17 where column "M"
is equal to the date (the cell above it), and add all of the values for those
rows' column "M".

Then, I don't know how to return the variables still. Here's what I've got
so far.

Function SalesTotal()

Dim varDate As Date
Dim LSearchRow, varSaleTotal As Integer

' Search date needs to be same column, one row up

varDate = Range((Target.Row - 1) & Target.Column).Value


On Error GoTo Err_Execute

' Setup search from sheet 18

Sheet18.Select

' yep, i have a title row
LSearchRow = 2

While Len(Range("A" & CInt(LSearchRow)).Value) > 0

If Range("M" & CInt(LSearchRow)).Value = varDate Then


'Add found range to var

varSaleTotal = varSaleTotal + (Range("O" &
CInt(LSearchRow)).Value)

'Go back to Sheet18 to keep looking

Sheet18.Select

End If

LSearchRow = LSearchRow + 1

Wend

Exit Function


Err_Execute:

MsgBox "An error occurred."


End Function

 

[Chris]

I've added a column "O" that has each row's total point value already there.
So now all I need is for it to find all the rows on sheet17 where column "M"
is equal to the date (the cell above it), and add all of the values for those
rows' column "M".

Then, I don't know how to return the variables still. Here's what I've got
so far.

Function SalesTotal()

Dim varDate As Date
Dim LSearchRow, varSaleTotal As Integer

' Search date needs to be same column, one row up

varDate = Range((Target.Row - 1) & Target.Column).Value


On Error GoTo Err_Execute

' Setup search from sheet 18

Sheet18.Select

' yep, i have a title row
LSearchRow = 2

While Len(Range("A" & CInt(LSearchRow)).Value) > 0

If Range("M" & CInt(LSearchRow)).Value = varDate Then


'Add found range to var

varSaleTotal = varSaleTotal + (Range("O" &
CInt(LSearchRow)).Value)

'Go back to Sheet18 to keep looking

Sheet18.Select

End If

LSearchRow = LSearchRow + 1

Wend

Exit Function


Err_Execute:

MsgBox "An error occurred."


End Function

 

[Chris]

and bump

 

[Chris]

and bump