| Home |
| Search |
| Today's Posts |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying formulas
Wow - I'll give it a try.
Thanks Stu "RagDyeR" wrote: How about making your formula shorter while still being able to copy down: =SUMPRODUCT(--(INDEX($2:$2,ROWS($1:3)):INDEX($31:$31,ROWS($1:3)) ={"s04","s05 ","s06","s07","s08","s09","s10","s11","s12"})) OR ... even: =SUM(COUNTIF(INDEX($2:$2,ROWS($1:3)):INDEX($31:$31 ,ROWS($1:3)),{"s04","s05", "s06","s07","s08","s09","s10","s11","s12"})) -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "Stu - Wast" wrote in message ... D'oh! Brilliant, that works perfect. Again, thanks very much. Now, I don't suppose you have any ideas for my other post - "Excel Ranges & LEFT" ??? Cheers Stu "Duke Carey" wrote: You're right that it's more complex. Howver, it does not look as though you used absolute row references as suggested. Change all the C2:C31 references to C$2:C$31 and try again. "Stu - Wast" wrote: Hi Duke, It did work for a simple formula. However, some of the formulas are quite complex : =COUNTIF(Planner!C2:C31,"s04")+COUNTIF(Planner!C2: C31,"s05")+COUNTIF(Planner !C2:C31,"s06")+COUNTIF(Planner!C2:C31,"s07")+COUNT IF(Planner!C2:C31,"s08")+C OUNTIF(Planner!C2:C31,"s09")+COUNTIF(Planner!C2:C3 1,"s10")+COUNTIF(Planner!C 2:C31,"s11")+COUNTIF(Planner!C2:C31,"s12") I can copy this, repeate it horizontally, but when I re-copy & transpose it back to the original cells, the "C2:C31" beocmes #REF!. I've tried pasting with different options - all, formula, formats, but none of them seem to copy just the formula as is - they all want to change the values. Any further help would be much appreciated. Thanks, Stu "Stu - Wast" wrote: Hi all, I have a row of forumulas, which relate to columns of data. Each formula needs to be copied down the sheet, but I need the column value to change, not the row value. eg C3 : =sum(c2:c31) C4 : =sum(d2:d31) C5: =sum(e2:e31) etc. How can I replicate the formulas down, but get the Column values to change ? TIA Stu |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excel Error when copying formulas | Excel Worksheet Functions | |||
| excel 2002 - copying formulas to another worksheet | Excel Discussion (Misc queries) | |||
| Excel 2002 - copying formulas across worksheets | Excel Discussion (Misc queries) | |||
| Copying options: contents, results, formulas, etc. | New Users to Excel | |||
| Excel & Copying Formulas | Excel Worksheet Functions | |||
Threaded Mode
