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

 

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

 

[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

 

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

 

[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

 

[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

 

[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

 

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

 

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

 

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