| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying formulas
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Copying formulas
Ignoring the issue of Circular references (the formula in C3 references a
range that includes C3)... I *think* this will work for you Put the formula =SUM(C$2:C$31) into an empty row in your spreadsheet and copy it across to all the columns you want to sum. Now copy all those formulas, select cell C3 and use Edit-Paste Special-Transpose "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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Copying formulas
Hi Duke,
The circular reference ignored the fact that the source cells are actually on a different sheet....., which I ommiteted from the formula. But I like the idea of the transpose - I think it should work as well... So, just the 25 columns on 12 spreadsheet to go then... THanks Stu "Duke Carey" wrote: Ignoring the issue of Circular references (the formula in C3 references a range that includes C3)... I *think* this will work for you Put the formula =SUM(C$2:C$31) into an empty row in your spreadsheet and copy it across to all the columns you want to sum. Now copy all those formulas, select cell C3 and use Edit-Paste Special-Transpose "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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Copying formulas
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(P lanner!C2:C31,"s07")+COUNTIF(Planner!C2:C31,"s08") +COUNTIF(Planner!C2:C31,"s09")+COUNTIF(Planner!C2: C31,"s10")+COUNTIF(Planner!C2:C31,"s11")+COUNTIF(P lanner!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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Copying formulas
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(P lanner!C2:C31,"s07")+COUNTIF(Planner!C2:C31,"s08") +COUNTIF(Planner!C2:C31,"s09")+COUNTIF(Planner!C2: C31,"s10")+COUNTIF(Planner!C2:C31,"s11")+COUNTIF(P lanner!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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Copying formulas
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(P lanner!C2:C31,"s07")+COUNTIF(Planner!C2:C31,"s08") +COUNTIF(Planner!C2:C31,"s09")+COUNTIF(Planner!C2: C31,"s10")+COUNTIF(Planner!C2:C31,"s11")+COUNTIF(P lanner!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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Copying formulas
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 |
|
#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 |
| Reply |
| 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 | |||
Linear Mode
