Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Crazy Hard Function
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Crazy Hard Function
would this:
=INDIRECT("Sheet18!"&CHAR(66+ROW())&ROW())*Sheet1! H1 help? then drag/copy down On 20 Maj, 06:36, Chris wrote: 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 * * * * * * * * *. * * * * * * * * *. * * * * * Sheet18.L$Row * Sheet1.H10 Add all those together, and that's the number I want to display. *The reason I need a function for it, is because I have two worksheets where this will be done 365 times (it's for a calendar). Any help is appreciated... As I said earlier, I'm at a loss here. Thanks |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Crazy Hard Function
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" wrote: would this: =INDIRECT("Sheet18!"&CHAR(66+ROW())&ROW())*Sheet1! H1 help? then drag/copy down On 20 Maj, 06:36, Chris wrote: 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 . . Sheet18.L$Row * Sheet1.H10 Add all those together, and that's the number I want to display. The reason I need a function for it, is because I have two worksheets where this will be done 365 times (it's for a calendar). Any help is appreciated... As I said earlier, I'm at a loss here. Thanks |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Crazy Hard Function
"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 On 20 Maj, 08:57, Chris wrote: 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" wrote: would this: =INDIRECT("Sheet18!"&CHAR(66+ROW())&ROW())*Sheet1! H1 help? then drag/copy down On 20 Maj, 06:36, Chris wrote: 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 Â* Â* Â* Â* Â* Â* Â* Â* Â*. Â* Â* Â* Â* Â* Â* Â* Â* Â*. Â* Â* Â* Â* Â* Sheet18.L$Row * Sheet1.H10 Add all those together, and that's the number I want to display. Â*The reason I need a function for it, is because I have two worksheets where this will be done 365 times (it's for a calendar). Any help is appreciated... As I said earlier, I'm at a loss here. Thanks- Ukryj cytowany tekst - - Pokaż cytowany tekst - |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Crazy Hard Function
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 (MVP - Excel) "Chris" wrote in message ... 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" wrote: would this: =INDIRECT("Sheet18!"&CHAR(66+ROW())&ROW())*Sheet1! H1 help? then drag/copy down On 20 Maj, 06:36, Chris wrote: 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 . . Sheet18.L$Row * Sheet1.H10 Add all those together, and that's the number I want to display. The reason I need a function for it, is because I have two worksheets where this will be done 365 times (it's for a calendar). Any help is appreciated... As I said earlier, I'm at a loss here. Thanks |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Crazy Hard Function
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 "Rick Rothstein" wrote: 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 (MVP - Excel) "Chris" wrote in message ... 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" wrote: would this: =INDIRECT("Sheet18!"&CHAR(66+ROW())&ROW())*Sheet1! H1 help? then drag/copy down On 20 Maj, 06:36, Chris wrote: 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 . . Sheet18.L$Row * Sheet1.H10 Add all those together, and that's the number I want to display. The reason I need a function for it, is because I have two worksheets where this will be done 365 times (it's for a calendar). Any help is appreciated... As I said earlier, I'm at a loss here. Thanks |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Crazy Hard Function
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 (MVP - Excel) "Chris" wrote in message ... 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 "Rick Rothstein" wrote: 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 (MVP - Excel) "Chris" wrote in message ... 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" wrote: would this: =INDIRECT("Sheet18!"&CHAR(66+ROW())&ROW())*Sheet1! H1 help? then drag/copy down On 20 Maj, 06:36, Chris wrote: 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 . . Sheet18.L$Row * Sheet1.H10 Add all those together, and that's the number I want to display. The reason I need a function for it, is because I have two worksheets where this will be done 365 times (it's for a calendar). Any help is appreciated... As I said earlier, I'm at a loss here. Thanks |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Crazy Hard Function
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" wrote in message ... 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 . . Sheet18.L$Row * Sheet1.H10 Add all those together, and that's the number I want to display. The reason I need a function for it, is because I have two worksheets where this will be done 365 times (it's for a calendar). Any help is appreciated... As I said earlier, I'm at a loss here. Thanks |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Crazy Hard Function
I'm not sure you understood, either :/
"Max" wrote: 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" wrote in message ... 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 . . Sheet18.L$Row * Sheet1.H10 Add all those together, and that's the number I want to display. The reason I need a function for it, is because I have two worksheets where this will be done 365 times (it's for a calendar). Any help is appreciated... As I said earlier, I'm at a loss here. Thanks |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Crazy Hard Function
"Chris" wrote:
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 --- |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Crazy Hard Function
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" wrote: I'm not sure you understood, either :/ "Max" wrote: 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" wrote in message ... 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 . . Sheet18.L$Row * Sheet1.H10 Add all those together, and that's the number I want to display. The reason I need a function for it, is because I have two worksheets where this will be done 365 times (it's for a calendar). Any help is appreciated... As I said earlier, I'm at a loss here. Thanks |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Crazy Hard Function
and bump
"Chris" wrote: 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 . . Sheet18.L$Row * Sheet1.H10 Add all those together, and that's the number I want to display. The reason I need a function for it, is because I have two worksheets where this will be done 365 times (it's for a calendar). Any help is appreciated... As I said earlier, I'm at a loss here. Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Crazy If | Excel Worksheet Functions | |||
Going CRAZY with INDEX and Match Function | Excel Worksheet Functions | |||
Loop gone crazy | Excel Discussion (Misc queries) | |||
I'm going crazy here | Excel Discussion (Misc queries) | |||
This is crazy!!! | Excel Discussion (Misc queries) |