Crazy Hard Function

[Chris]

Well, as the title says, I've got a hard function, and I'm at a loss, so here
goes what I'm trying to code.

This function will be on Sheet20.

Where cell "F4" is found in column "M" of Sheet18, I need it to multiply
that row's column "C" by the value of Sheet1's "H1"... Let me try and type it
another way.

If Sheet20.$$ == Sheet18.M$row <--- I'm implying that I need all rows where
Sheet18's column "M" has the same value as a value on Sheet20.

Sheet18.C$row * Sheet1.H1
Sheet18.D$row * Sheet1.H2

 

[Jarek Kujawa]

would this:

=INDIRECT("Sheet18!"&CHAR(66+ROW())&ROW())*Sheet1!H1

help?

then drag/copy down

 

[Chris]

I'm not sure of a couple things here... First, can you break this down for
me, because I may be able to use it then. I'm just unfamiliar with these
functions still.

Thanks again.

 

[Jarek Kujawa]

"C" has an ASCII code of 67 and may be expressed as CHAR(67), ROW() is
the current row
presuming your formula Sheet18.C$row * Sheet1.H1 is in row 1
C=CHAR(66+ROW())=CHAR(67) in row 1
D=CHAR(66+ROW())=CHAR(68) in row 2
....
INDIRECT function is used for constructing addresses of text strings
thus "Sheet18!C$" may be expressed as INDIRECT("Sheet18!"&CHAR(66+ROW
())...
& is used to concatenate text strings

HIH

 

[Rick Rothstein]

While you are waiting for Jarek to respond to you, I have a couple of
questions of my own for you. I found your original description a little bit
"fuzzy" and, from the wording of Jarek's response, I'm guessing he did too.
What is "fixed" in your specified data and what is "changeable"? By that I
mean... will the formula we develop only be used in a single cell or will it
be copied to other cells on Sheet20? If copied, what is "fixed" in the
calculation and what is "changeable"? Again, by that I mean... is the value
in Sheet20!F4 that you are having Column M of Sheet18 match always going to
be the value from Sheet20!F4, or will it be some other value when the
formula is copied? The columns C through L on Sheet18 for the "found rows"
is obviously fixed, but is the Column H (rows 1 through 10, which are fixed)
on Sheet 1 also fixed, or will this column letter change when the formula is
copied?

 

[Chris]

Oh boy.. I figured I was gonna have to get messy with this.

Sheet1 (Summary) is a sheet that displays a summary of 2 weeks of sales
information.
Sheet2 - Sheet15 (titles change depending on dates covered at the time) are
where the user would input the sales information (account number, quantities,
and install date).

Sheet16 (Current Period) Linked cells from Sheet2 - Sheet15

Sheet18 (All) A backup, where Sheet16 contents are copied, and empty rows
are removed.

Sheet20 (2009) A calendar where below each date (and yes, I typed all 365
dates in there) I want a function that will search for all rows in Sheet18
where column M (the install date) is equal to the date on this sheet,
multiply the quantities (columns C - L of said rows) by the amount of points
they are worth (Sheet1, fields H1-H10) and add all them together for a total
point count.

I hope all of this makes sense... It does in my head but trying to express
this in words is a pain.

Thanks

 

[Max]

Just another venture hazarded ..

In Sheet20,
Place in say, G4, then copy down to G13:
=SUMPRODUCT((Sheet18!M$2:M$100=F$4)*OFFSET(Sheet18!C$2:C$100,,ROWS($1:1)-1,))*Sheet1!H1

Then a simple summation in G14: =SUM(G4:G13)
derives the final answer?
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:370 Subscribers:68
xdemechanik

 

[Chris]

I'm not sure you understood, either :/

Just another venture hazarded ..

In Sheet20,
Place in say, G4, then copy down to G13:
=SUMPRODUCT((Sheet18!M$2:M$100=F$4)*OFFSET(Sheet18!C$2:C$100,,ROWS($1:1)-1,))*Sheet1!H1

Then a simple summation in G14: =SUM(G4:G13)
derives the final answer?
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:370 Subscribers:68
xdemechanik

 

[Rick Rothstein]

Can you make use of a User Defined Function (UDF)? If so...

Function TotalPoints() As Double
Dim X As Long, Z As Long, LastCell As Long
Dim S1 As Worksheet, S18 As Worksheet, S20 As Worksheet
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 S18.Cells(X, "M").Value = S20.Cells(4, _
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

To install this function, press Alt+F11 from a 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. Before going back to the
worksheet to use this function, first make sure I guessed correctly that the
dates on Sheet20 that you want to match are located in Row 4. If they are
not on Row 4, then change the 4 in the first If statement to the actual row
number. Now, go back to your worksheet, put this formula under the first
date...

=TotalPoints()

and copy it across as needed.

 

[Max]

I'm not sure you understood, either :/

Ahh, I don't know. Thought that was a pretty good shot at resolving your
issue as you originally posted, albeit in 2 steps. Tell me, did you try it
out (after adapting the ranges in the 1st expression to suit the actual
extents of your data in Sheet18)? And what was wrong with the result derived
in G14 based on your actuals?
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:370 Subscribers:68
xdemechanik
---

 

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

 

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

 

[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]

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