Copying formulas

S

Stu - Wast

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
 
D

Duke Carey

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
 
S

Stu - Wast

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
 
S

Stu - Wast

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")+COUNTIF(Planner!C2:C31,"s08")+COUNTIF(Planner!C2:C31,"s09")+COUNTIF(Planner!C2:C31,"s10")+COUNTIF(Planner!C2: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
 
D

Duke Carey

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.
 
S

Stu - Wast

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
 
R

RagDyeR

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!
=====================================================


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
 
S

Stu - Wast

Wow - I'll give it a try.

Thanks

Stu

RagDyeR said:
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!
=====================================================


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
Click to expand...
 
Top