Paste Link Problem

[Shirley Munro]

I have a workbook containing several worksheet. The first sheet is named Production Output and contains totals which I require in another worksheet.
The details in the Production Output sheet are as follows:

cell D21 contains the value £22,756
Cell F21 contains the value £32,988
Cell H21 contains the value £53,537
Cell J21 contains the value £58, 350

I was to copy these values into another worksheet starting in cell B3 and then continuing down in cells B4, B5, B6 etc

The formula in B3 is therefore =ProductionOutput!D$21 and the formula in B4 is =ProductionOutput!F$21. I now select cell B3 and B4 and want to copy this formula down cells B5 onwards but when I do this it replicates the formula in B3 and B4 and does not increment it. I have also tried putting the answers along row 3 rather than down column B but the following answers appear:

in cell B3 I have the formula =ProductionOutput!D$21
in cell C3 I have the formula =ProductionOutput!F$21


When I copy this along row 3, cell D3 contains the formula ProductionOutput!F$21 and then cell E3 contains the formula =ProductionOutput!H$21. Cell F3 then repeats the formula in E3 (=ProductionOutput!H$21) and so it continues. Obviously I want the formulas to be:

=ProductionOutput!D$21
=ProductionOutput!F$21
=ProductionOutput!H$21
=ProductionOutput!J$21

and so on.

Any help would be much appreciated.

Shirley Munro

[flummi]

If you copy your formulas down the row part of the references can't
change because the have a $-sign in front of them.

If you want the Column parts to be adapted automatically I would
suggest you copy the formulas as follows

Formula in B4: ='Production Output'!D$21

B4 --> D4
D4 --> F4
F4 --> H4

Then you MOVE

D4 to B5
F4 to B6
H4 to B7

Is that the end of the story or do you then want to copy that block
elsewhere?

Hans

[Dav]

If you wish to go across the sheet put in b3
=OFFSET(Productionoutput!$E$23,0,(COLUMN()-2)*2)

If you wish to go down the sheet try
=OFFSET(Productionoutput!$E$23,0,(ROW()-3)*2)

It is because you want to skip a row between each formula which produces the problem, the previous suggestion is simpler if you do not have many to do.

If both this instances the row and column bit increase by 2 so copying them increases the cell return by 2, which has the effect of skiping every other column

Regards

Dav

[Shirley Munro]

Thanks Dav. This worked perfectly.

Shirley Munro