Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup and Sum
I am trying to look up and sum multiple values using 2 different criteria. I
then want to put that sum in a cell on another sheet. I am currently using this formula, which works fine: =SUM(IF((Received!$B$8:$B$140=G$2)*(Received!$C$8: $C$140=Project),Received!$E$8:$E$140)) (entered with ctrl+shft+entr) The problem is that I have 2100 similar formulas on one sheet. I'd like to write VBA code to loop through this so the average user can't mess up the formula by simply clicking on a cell! If someone could help me determine how to do this for one column I'm sure I could apply as needed through the rest of the sheet. I'm not a VBA beginner but not an expert either. The above formula is currently in cell G5 of my summary sheet. It extends down to G173, but in every row the If True value changes columns. For instance in G6 the formula looks like this: =SUM(IF((Received!$B$8:$B$140=G$2)*(Received!$C$8: $C$140=Project),Received!$F$8:$F$140)) Would I use a loop or the Do function to run this down to G173 in my summary sheet and out to column FE in the formula? I just need a little help getting started! Thanks. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup and Sum
Do you mean something like
ColNum = 5 For i = 5 To 173 Cells(i,"G").FiormulaR1C1 = "=SUMPRODUCT((Received!R8C2:R140C2=R2C7)*(Received !$R8C3:R140C3=Project),Received!R8C" & ColNum & ":R140C" & ColNum & ")" colNum = colNum + 1 Next i -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Shannon" wrote in message ... I am trying to look up and sum multiple values using 2 different criteria. I then want to put that sum in a cell on another sheet. I am currently using this formula, which works fine: =SUM(IF((Received!$B$8:$B$140=G$2)*(Received!$C$8: $C$140=Project),Received!$E$8:$E$140)) (entered with ctrl+shft+entr) The problem is that I have 2100 similar formulas on one sheet. I'd like to write VBA code to loop through this so the average user can't mess up the formula by simply clicking on a cell! If someone could help me determine how to do this for one column I'm sure I could apply as needed through the rest of the sheet. I'm not a VBA beginner but not an expert either. The above formula is currently in cell G5 of my summary sheet. It extends down to G173, but in every row the If True value changes columns. For instance in G6 the formula looks like this: =SUM(IF((Received!$B$8:$B$140=G$2)*(Received!$C$8: $C$140=Project),Received!$F$8:$F$140)) Would I use a loop or the Do function to run this down to G173 in my summary sheet and out to column FE in the formula? I just need a little help getting started! Thanks. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup and Sum
Hi Bob,
Thanks for your help. In theory this looks like it would work, but in practice I'm getting a definition error. I'm assuming the i in "Fiormula" is a typo but otherwise copying precisely what you have into my code. The first time I forgot the second = and the code put the formula into each cell but without the necessary = obviously, so it was all just text. Once I corrected that mistake I began getting the object or application definition error. Any ideas? Thanks for your time, Shannon "Bob Phillips" wrote: Do you mean something like ColNum = 5 For i = 5 To 173 Cells(i,"G").FiormulaR1C1 = "=SUMPRODUCT((Received!R8C2:R140C2=R2C7)*(Received !$R8C3:R140C3=Project),Received!R8C" & ColNum & ":R140C" & ColNum & ")" colNum = colNum + 1 Next i -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Shannon" wrote in message ... I am trying to look up and sum multiple values using 2 different criteria. I then want to put that sum in a cell on another sheet. I am currently using this formula, which works fine: =SUM(IF((Received!$B$8:$B$140=G$2)*(Received!$C$8: $C$140=Project),Received!$E$8:$E$140)) (entered with ctrl+shft+entr) The problem is that I have 2100 similar formulas on one sheet. I'd like to write VBA code to loop through this so the average user can't mess up the formula by simply clicking on a cell! If someone could help me determine how to do this for one column I'm sure I could apply as needed through the rest of the sheet. I'm not a VBA beginner but not an expert either. The above formula is currently in cell G5 of my summary sheet. It extends down to G173, but in every row the If True value changes columns. For instance in G6 the formula looks like this: =SUM(IF((Received!$B$8:$B$140=G$2)*(Received!$C$8: $C$140=Project),Received!$F$8:$F$140)) Would I use a loop or the Do function to run this down to G173 in my summary sheet and out to column FE in the formula? I just need a little help getting started! Thanks. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup and Sum
I left a $ sign in there
For i = 5 To 173 Cells(i, "G").FormulaR1C1 = "=SUMPRODUCT((Received!R8C2:R140C2=R2C7)*" & _ "(Received!R8C3:R140C3=Project),Received!R8C" & _ colNum & ":R140C" & colNum & ")" colNum = colNum + 1 Next i -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Shannon" wrote in message ... Hi Bob, Thanks for your help. In theory this looks like it would work, but in practice I'm getting a definition error. I'm assuming the i in "Fiormula" is a typo but otherwise copying precisely what you have into my code. The first time I forgot the second = and the code put the formula into each cell but without the necessary = obviously, so it was all just text. Once I corrected that mistake I began getting the object or application definition error. Any ideas? Thanks for your time, Shannon "Bob Phillips" wrote: Do you mean something like ColNum = 5 For i = 5 To 173 Cells(i,"G").FiormulaR1C1 = "=SUMPRODUCT((Received!R8C2:R140C2=R2C7)*(Received !$R8C3:R140C3=Project),Received!R8C" & ColNum & ":R140C" & ColNum & ")" colNum = colNum + 1 Next i -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Shannon" wrote in message ... I am trying to look up and sum multiple values using 2 different criteria. I then want to put that sum in a cell on another sheet. I am currently using this formula, which works fine: =SUM(IF((Received!$B$8:$B$140=G$2)*(Received!$C$8: $C$140=Project),Received!$E$8:$E$140)) (entered with ctrl+shft+entr) The problem is that I have 2100 similar formulas on one sheet. I'd like to write VBA code to loop through this so the average user can't mess up the formula by simply clicking on a cell! If someone could help me determine how to do this for one column I'm sure I could apply as needed through the rest of the sheet. I'm not a VBA beginner but not an expert either. The above formula is currently in cell G5 of my summary sheet. It extends down to G173, but in every row the If True value changes columns. For instance in G6 the formula looks like this: =SUM(IF((Received!$B$8:$B$140=G$2)*(Received!$C$8: $C$140=Project),Received!$F$8:$F$140)) Would I use a loop or the Do function to run this down to G173 in my summary sheet and out to column FE in the formula? I just need a little help getting started! Thanks. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup and Sum
Wow, I should have noticed that! Thanks, Bob, it works fine. I've got it
setup to run through the formulas and then copy and paste values so the user can't see the formulas. The only problem is that it takes around 10 seconds to process all of this. Is there an easier/quicker way? Thanks, Shannon "Bob Phillips" wrote: I left a $ sign in there For i = 5 To 173 Cells(i, "G").FormulaR1C1 = "=SUMPRODUCT((Received!R8C2:R140C2=R2C7)*" & _ "(Received!R8C3:R140C3=Project),Received!R8C" & _ colNum & ":R140C" & colNum & ")" colNum = colNum + 1 Next i -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Shannon" wrote in message ... Hi Bob, Thanks for your help. In theory this looks like it would work, but in practice I'm getting a definition error. I'm assuming the i in "Fiormula" is a typo but otherwise copying precisely what you have into my code. The first time I forgot the second = and the code put the formula into each cell but without the necessary = obviously, so it was all just text. Once I corrected that mistake I began getting the object or application definition error. Any ideas? Thanks for your time, Shannon "Bob Phillips" wrote: Do you mean something like ColNum = 5 For i = 5 To 173 Cells(i,"G").FiormulaR1C1 = "=SUMPRODUCT((Received!R8C2:R140C2=R2C7)*(Received !$R8C3:R140C3=Project),Received!R8C" & ColNum & ":R140C" & ColNum & ")" colNum = colNum + 1 Next i -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Shannon" wrote in message ... I am trying to look up and sum multiple values using 2 different criteria. I then want to put that sum in a cell on another sheet. I am currently using this formula, which works fine: =SUM(IF((Received!$B$8:$B$140=G$2)*(Received!$C$8: $C$140=Project),Received!$E$8:$E$140)) (entered with ctrl+shft+entr) The problem is that I have 2100 similar formulas on one sheet. I'd like to write VBA code to loop through this so the average user can't mess up the formula by simply clicking on a cell! If someone could help me determine how to do this for one column I'm sure I could apply as needed through the rest of the sheet. I'm not a VBA beginner but not an expert either. The above formula is currently in cell G5 of my summary sheet. It extends down to G173, but in every row the If True value changes columns. For instance in G6 the formula looks like this: =SUM(IF((Received!$B$8:$B$140=G$2)*(Received!$C$8: $C$140=Project),Received!$F$8:$F$140)) Would I use a loop or the Do function to run this down to G173 in my summary sheet and out to column FE in the formula? I just need a little help getting started! Thanks. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup and Sum
10 seconds to create the formulae, or 10 secs to recalculate.
If the former, try this Application.ScreenUpdating = False Application.Calculation = xlCalculationManual For i = 5 To 173 Cells(i, "G").FormulaR1C1 = _ "=SUMPRODUCT((Received!R8C2:R140C2=R2C7)*" & _ "(Received!R8C3:R140C3=Project),Received!R8C" & _ colNum & ":R140C" & colNum & ")" colNum = colNum + 1 Next i Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Shannon" wrote in message ... Wow, I should have noticed that! Thanks, Bob, it works fine. I've got it setup to run through the formulas and then copy and paste values so the user can't see the formulas. The only problem is that it takes around 10 seconds to process all of this. Is there an easier/quicker way? Thanks, Shannon "Bob Phillips" wrote: I left a $ sign in there For i = 5 To 173 Cells(i, "G").FormulaR1C1 = "=SUMPRODUCT((Received!R8C2:R140C2=R2C7)*" & _ "(Received!R8C3:R140C3=Project),Received!R8C" & _ colNum & ":R140C" & colNum & ")" colNum = colNum + 1 Next i -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Shannon" wrote in message ... Hi Bob, Thanks for your help. In theory this looks like it would work, but in practice I'm getting a definition error. I'm assuming the i in "Fiormula" is a typo but otherwise copying precisely what you have into my code. The first time I forgot the second = and the code put the formula into each cell but without the necessary = obviously, so it was all just text. Once I corrected that mistake I began getting the object or application definition error. Any ideas? Thanks for your time, Shannon "Bob Phillips" wrote: Do you mean something like ColNum = 5 For i = 5 To 173 Cells(i,"G").FiormulaR1C1 = "=SUMPRODUCT((Received!R8C2:R140C2=R2C7)*(Received !$R8C3:R140C3=Project),Received!R8C" & ColNum & ":R140C" & ColNum & ")" colNum = colNum + 1 Next i -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Shannon" wrote in message ... I am trying to look up and sum multiple values using 2 different criteria. I then want to put that sum in a cell on another sheet. I am currently using this formula, which works fine: =SUM(IF((Received!$B$8:$B$140=G$2)*(Received!$C$8: $C$140=Project),Received!$E$8:$E$140)) (entered with ctrl+shft+entr) The problem is that I have 2100 similar formulas on one sheet. I'd like to write VBA code to loop through this so the average user can't mess up the formula by simply clicking on a cell! If someone could help me determine how to do this for one column I'm sure I could apply as needed through the rest of the sheet. I'm not a VBA beginner but not an expert either. The above formula is currently in cell G5 of my summary sheet. It extends down to G173, but in every row the If True value changes columns. For instance in G6 the formula looks like this: =SUM(IF((Received!$B$8:$B$140=G$2)*(Received!$C$8: $C$140=Project),Received!$F$8:$F$140)) Would I use a loop or the Do function to run this down to G173 in my summary sheet and out to column FE in the formula? I just need a little help getting started! Thanks. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup and Sum
Thanks Bob. It moves a lot quicker now. I appreciate all your help.
-Shannon "Bob Phillips" wrote: 10 seconds to create the formulae, or 10 secs to recalculate. If the former, try this Application.ScreenUpdating = False Application.Calculation = xlCalculationManual For i = 5 To 173 Cells(i, "G").FormulaR1C1 = _ "=SUMPRODUCT((Received!R8C2:R140C2=R2C7)*" & _ "(Received!R8C3:R140C3=Project),Received!R8C" & _ colNum & ":R140C" & colNum & ")" colNum = colNum + 1 Next i Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Shannon" wrote in message ... Wow, I should have noticed that! Thanks, Bob, it works fine. I've got it setup to run through the formulas and then copy and paste values so the user can't see the formulas. The only problem is that it takes around 10 seconds to process all of this. Is there an easier/quicker way? Thanks, Shannon "Bob Phillips" wrote: I left a $ sign in there For i = 5 To 173 Cells(i, "G").FormulaR1C1 = "=SUMPRODUCT((Received!R8C2:R140C2=R2C7)*" & _ "(Received!R8C3:R140C3=Project),Received!R8C" & _ colNum & ":R140C" & colNum & ")" colNum = colNum + 1 Next i -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Shannon" wrote in message ... Hi Bob, Thanks for your help. In theory this looks like it would work, but in practice I'm getting a definition error. I'm assuming the i in "Fiormula" is a typo but otherwise copying precisely what you have into my code. The first time I forgot the second = and the code put the formula into each cell but without the necessary = obviously, so it was all just text. Once I corrected that mistake I began getting the object or application definition error. Any ideas? Thanks for your time, Shannon "Bob Phillips" wrote: Do you mean something like ColNum = 5 For i = 5 To 173 Cells(i,"G").FiormulaR1C1 = "=SUMPRODUCT((Received!R8C2:R140C2=R2C7)*(Received !$R8C3:R140C3=Project),Received!R8C" & ColNum & ":R140C" & ColNum & ")" colNum = colNum + 1 Next i -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Shannon" wrote in message ... I am trying to look up and sum multiple values using 2 different criteria. I then want to put that sum in a cell on another sheet. I am currently using this formula, which works fine: =SUM(IF((Received!$B$8:$B$140=G$2)*(Received!$C$8: $C$140=Project),Received!$E$8:$E$140)) (entered with ctrl+shft+entr) The problem is that I have 2100 similar formulas on one sheet. I'd like to write VBA code to loop through this so the average user can't mess up the formula by simply clicking on a cell! If someone could help me determine how to do this for one column I'm sure I could apply as needed through the rest of the sheet. I'm not a VBA beginner but not an expert either. The above formula is currently in cell G5 of my summary sheet. It extends down to G173, but in every row the If True value changes columns. For instance in G6 the formula looks like this: =SUM(IF((Received!$B$8:$B$140=G$2)*(Received!$C$8: $C$140=Project),Received!$F$8:$F$140)) Would I use a loop or the Do function to run this down to G173 in my summary sheet and out to column FE in the formula? I just need a little help getting started! Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
lookup help. lookup result based on data in 2 columns | Excel Worksheet Functions | |||
LOOKUP FUNCTION? (LOOKUP VALUE BEING A TIME RENERATED FROM A FORMU | Excel Discussion (Misc queries) | |||
Get Cell Address From Lookup (Alternative to Lookup) | Excel Worksheet Functions | |||
Join 2 Lists - Lookup value in 1 list & use result in 2nd lookup | Excel Worksheet Functions | |||
Pivot table doing a lookup without using the lookup function? | Excel Discussion (Misc queries) |