|
Question about a formula
I trying to fig out what the formula is doing/
FormulaR1C1 = "=SUMIF(TOEPIEXP!C[2],1,TOEPIEXP!C[40])" TOEPIEXP!C i know that TOEPIEXP is the worksheet what is the C? [2],1,TOEPIEXP!C[40])" What is the [2] ? The 1 is the criteria. TOEPIEXP is the work sheet. What is C? what is 40? -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200612/1 |
Question about a formula
jln
The [C]2 is R1C1 notation, the C is for 'Column' and the 2 is Column 2 so B and as it has no '[R]' refers to the whole column. (Lookup Range) The 1 is the criteria and the second [C]40 is the range that is summed Column 40 -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "jln via OfficeKB.com" <u25956@uwe wrote in message news:6afe660e3e8b6@uwe... I trying to fig out what the formula is doing/ FormulaR1C1 = "=SUMIF(TOEPIEXP!C[2],1,TOEPIEXP!C[40])" TOEPIEXP!C i know that TOEPIEXP is the worksheet what is the C? [2],1,TOEPIEXP!C[40])" What is the [2] ? The 1 is the criteria. TOEPIEXP is the work sheet. What is C? what is 40? -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200612/1 |
Question about a formula
jln
Just spotted I have brackets all over the place, they should be around the number...sorry -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "Nick Hodge" wrote in message ... jln The [C]2 is R1C1 notation, the C is for 'Column' and the 2 is Column 2 so B and as it has no '[R]' refers to the whole column. (Lookup Range) The 1 is the criteria and the second [C]40 is the range that is summed Column 40 -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "jln via OfficeKB.com" <u25956@uwe wrote in message news:6afe660e3e8b6@uwe... I trying to fig out what the formula is doing/ FormulaR1C1 = "=SUMIF(TOEPIEXP!C[2],1,TOEPIEXP!C[40])" TOEPIEXP!C i know that TOEPIEXP is the worksheet what is the C? [2],1,TOEPIEXP!C[40])" What is the [2] ? The 1 is the criteria. TOEPIEXP is the work sheet. What is C? what is 40? -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200612/1 |
Question about a formula
Nick that what i thought but im not using column 2 im using 6. I have
recorded for about 20 lines all using column 6 and they all say 2. SO how can that be the column number? Nick Hodge wrote: jln The [C]2 is R1C1 notation, the C is for 'Column' and the 2 is Column 2 so B and as it has no '[R]' refers to the whole column. (Lookup Range) The 1 is the criteria and the second [C]40 is the range that is summed Column 40 I trying to fig out what the formula is doing/ [quoted text clipped - 5 lines] is the work sheet. What is C? what is 40? -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200612/1 |
Question about a formula
jln
Using R1C1 notation, everything is relative to the range the formula is in. you have clipped the part before FormulaR1C1 If this was Range("C1").FormulaR1C1 then C[2] would refer to Column E Does that make sense? -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "jln via OfficeKB.com" <u25956@uwe wrote in message news:6afea1e594442@uwe... Nick that what i thought but im not using column 2 im using 6. I have recorded for about 20 lines all using column 6 and they all say 2. SO how can that be the column number? Nick Hodge wrote: jln The [C]2 is R1C1 notation, the C is for 'Column' and the 2 is Column 2 so B and as it has no '[R]' refers to the whole column. (Lookup Range) The 1 is the criteria and the second [C]40 is the range that is summed Column 40 I trying to fig out what the formula is doing/ [quoted text clipped - 5 lines] is the work sheet. What is C? what is 40? -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200612/1 |
Question about a formula
Here is what it looks like when i record it
Range("D27").Select ActiveCell.FormulaR1C1 = "=SUMIF(TOEPIEXP!C[2],1,TOEPIEXP!C[34])-R[-2]C-R [-1]C" Nick Hodge wrote: jln Using R1C1 notation, everything is relative to the range the formula is in. you have clipped the part before FormulaR1C1 If this was Range("C1").FormulaR1C1 then C[2] would refer to Column E Does that make sense? Nick that what i thought but im not using column 2 im using 6. I have recorded for about 20 lines all using column 6 and they all say 2. SO how [quoted text clipped - 15 lines] is the work sheet. What is C? what is 40? -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200612/1 |
Question about a formula
The formula is displaying a relative relationship to the column that the
formula is in. For example: If your formula is in column 4 then 2 columns to the right is column 6. 40 columns to the right is column 44. Mike F "jln via OfficeKB.com" <u25956@uwe wrote in message news:6afea1e594442@uwe... Nick that what i thought but im not using column 2 im using 6. I have recorded for about 20 lines all using column 6 and they all say 2. SO how can that be the column number? Nick Hodge wrote: jln The [C]2 is R1C1 notation, the C is for 'Column' and the 2 is Column 2 so B and as it has no '[R]' refers to the whole column. (Lookup Range) The 1 is the criteria and the second [C]40 is the range that is summed Column 40 I trying to fig out what the formula is doing/ [quoted text clipped - 5 lines] is the work sheet. What is C? what is 40? -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200612/1 |
Question about a formula
jln
Then [2] refers to F, 2 columns to the right of D (D27) -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "jln via OfficeKB.com" <u25956@uwe wrote in message news:6afed105b6b42@uwe... Here is what it looks like when i record it Range("D27").Select ActiveCell.FormulaR1C1 = "=SUMIF(TOEPIEXP!C[2],1,TOEPIEXP!C[34])-R[-2]C-R [-1]C" Nick Hodge wrote: jln Using R1C1 notation, everything is relative to the range the formula is in. you have clipped the part before FormulaR1C1 If this was Range("C1").FormulaR1C1 then C[2] would refer to Column E Does that make sense? Nick that what i thought but im not using column 2 im using 6. I have recorded for about 20 lines all using column 6 and they all say 2. SO how [quoted text clipped - 15 lines] is the work sheet. What is C? what is 40? -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200612/1 |
| All times are GMT +1. The time now is 03:11 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com