Copying absolute cell references

[xelhelp]

Hi,

I have a spreadsheet that has some values in cells A1:E1 (let's call these base values 1)

The values in cells A2:A10 are calculated using the base values in A1:E1

So, the formulas is A2:A10 might look something like this...

A2 = ($A$1 + $B$1 + $C$1 + $D$1 + $E$1) * 1
A3 = ($A$1 + $B$1 + $C$1 + $D$1 + $E$1) * 2
A4 = ($A$1 + $B$1 + $C$1 + $D$1 + $E$1) * 3
...and so on


Now all I want to do is to copy cells A1:E10 and paste it starting at cell A11. I will change the values in A11:E11 (these will be new base values) that were copied from above, and the values in A12:A20 need to be calculated using the values in A11:E11, NOT A1:E1. The problem is that when I copy A1:E10 and paste starting in A11, the values in A12:A20 look like this...

A12 = ($A$1 + $B$1 + $C$1 + $D$1 + $E$1) * 1
A13 = ($A$1 + $B$1 + $C$1 + $D$1 + $E$1) * 2
A14 = ($A$1 + $B$1 + $C$1 + $D$1 + $E$1) * 3
...

I want it to look like this...
A12 = ($A$11 + $B$11 + $C$11 + $D$11 + $E$11) * 1
A13 = ($A$11 + $B$11 + $C$11 + $D$11 + $E$11) * 2
A14 = ($A$11 + $B$11 + $C$11 + $D$11 + $E$11) * 3
...


So it's referencing the cells for base values 1 not base values 2. I need to be able to copy and paste sections like this down the spreadsheet, but I need the absolute reference to reference the "local" absolute references.

Thanks

 

[BenMiller]

Change your original formulas to have absolute column references but not absolute row references. E.G. =($A1+$B1+$C1+$D1+$E1)*1

 

[xelhelp]

but when I copy the formulas down cells A2:A10 it looses the A1:E1 cell references ...

 

[BenMiller]

I thought A2:A10 have different formulas? Why are you dragging those?

 

[xelhelp]

sorry i should correct my self, the formulas in A2:A10 look like this...


A2 = ($A$1 + $B$1 + $C$1 + $D$1 + $E$1) * B2
A3 = ($A$1 + $B$1 + $C$1 + $D$1 + $E$1) * B3
A4 = ($A$1 + $B$1 + $C$1 + $D$1 + $E$1) * B4
...

B2:B10 might have numbers in them, lets say 2-9

So I need to be able to copy/drag down A2:A10

 

[xelhelp]

Is there a way to do this?...Besides copying sections and then changing the cell references of the first line in each section with calculations (i.e. A2, A12, A22...). Then copy/drag the formulas down each section.

thanks