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

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

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

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

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

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

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

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