Copying a Formula which contains a link to another worksheet

[Shirley Munro]

Hi


I am having problems copying a formula which contains a link to another worksheet in the same workbook. One worksheet contains figures that need to be copied into another worksheet for the purpose of this question I have called it Sheet 1. The first figure to be copied is in cell D3, the next figure in cell F3, the next figure in cell H3 and so on. The second sheet wants to create a formula which brings in the figures from Sheet 1 starting in column B, cell 3 and then continuing down column B. The first formula I have entered is ='Sheet1'!D$3, the second formula I have entered is ='Sheet1'!F$3. I now select the 2 cells containing the formula and when I copy this down I want it to become 'Sheet1!H$3, 'Sheet1'!J$3, 'Sheet1'!L$3, 'Sheet1'!N$3 etc but this isn't happening. Instead I just keep getting ='Sheet1'!D$3 and ='Sheet1'!F$3 repeated down the column.

Any suggestions would be appreciated.

Shirley Munro

[wAyne]

Shirley -- if you copy down it changes the rows not the columns.
I think I have a way arouinf this... if you take yourformulas copy them
across columns it will change the calues to "=Sheet1!B$1", "=Sheet1!C$1" etc.
thna if you replace all "=Sheet1" with "Sheet 1", copy and paste the values
to the column you want, use paste special and "Transpose" this will take the
columns and put them in a row. Then redo your replace "Sheet1" to "=Sheet1"
all should be well.

I tried to do this without replacing the formulas, but iot still changes
them to B1, B2. etc.

hope this helps
wAyne

"Shirley Munro" wrote:

>
> Hi
>
>
> I am having problems copying a formula which contains a link to another
> worksheet in the same workbook. One worksheet contains figures that
> need to be copied into another worksheet for the purpose of this
> question I have called it Sheet 1. The first figure to be copied is in
> cell D3, the next figure in cell F3, the next figure in cell H3 and so
> on. The second sheet wants to create a formula which brings in the
> figures from Sheet 1 starting in column B, cell 3 and then continuing
> down column B. The first formula I have entered is ='Sheet1'!D$3, the
> second formula I have entered is ='Sheet1'!F$3. I now select the 2
> cells containing the formula and when I copy this down I want it to
> become 'Sheet1!H$3, 'Sheet1'!J$3, 'Sheet1'!L$3, 'Sheet1'!N$3 etc but
> this isn't happening. Instead I just keep getting ='Sheet1'!D$3 and
> ='Sheet1'!F$3 repeated down the column.
>
> Any suggestions would be appreciated.
>
> Shirley Munro
>
>
> --
> Shirley Munro
> ------------------------------------------------------------------------
> Shirley Munro's Profile: http://www.excelforum.com/member.php...nfo&userid=836
> View this thread: http://www.excelforum.com/showthread...hreadid=507595
>
>

[Shirley Munro]

Hi

Thanks for your reply but I have tried creating the formulas along the row rather than down the column. Assume I am starting in cell B2. The formula for this cell is ='Sheet1'!D3 and the formula in C2 is ='Sheet1'!F3. because I am now copying along the row rather than down the column I have no need to make any part of the cell absolute. I am then selecting cells B2 and C2 and dragging these across cells D2 onwards. However, what happens is that when I copy this across the formula that appears in D2 is exactly the same as that in C2 (='Sheet1'F3) but the formula in E2 then changes to ='Sheet1'!H3. I want the formula in C2 to become ='Sheet1'!H3 and the formula in D2 to become ='Sheet2'!J3 and so on.

Any more suggestions would be good.

Shirley

[David Biddulph]

"Shirley Munro" <[email protected]>
wrote in message
news:[email protected]...

> I am having problems copying a formula which contains a link to another
> worksheet in the same workbook. One worksheet contains figures that
> need to be copied into another worksheet for the purpose of this
> question I have called it Sheet 1. The first figure to be copied is in
> cell D3, the next figure in cell F3, the next figure in cell H3 and so
> on. The second sheet wants to create a formula which brings in the
> figures from Sheet 1 starting in column B, cell 3 and then continuing
> down column B. The first formula I have entered is ='Sheet1'!D$3, the
> second formula I have entered is ='Sheet1'!F$3. I now select the 2
> cells containing the formula and when I copy this down I want it to
> become 'Sheet1!H$3, 'Sheet1'!J$3, 'Sheet1'!L$3, 'Sheet1'!N$3 etc but
> this isn't happening. Instead I just keep getting ='Sheet1'!D$3 and
> ='Sheet1'!F$3 repeated down the column.

If you are trying to refer from alternate columns in your data sheet & pick
them up in consecutive rows in your second sheet starting from cell B3, you
may be easier using a formula like:
=OFFSET(Sheet1!D$3,0,(ROW()-3)*2)
--
David Biddulph

[Shirley Munro]

Hi David

I think I am now possibly on the right lines but when I use the formula you provided, it is still giving me a 0 value in every second row. Unfortunately I don't seem to be able to insert a screen shot of my data in here but can I give you more details and the exact cell addresses.

The details in the first 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 start in cell B3 and then continuing down in cells B4, B5, B6 etc

When I use the OFFSET formula you sent me changing row 3 to row 21

=OFFSET('Production Output'!D$21,0,(ROW()-3*2))

the value in B3 appears is the text contained in A21 of my worksheet.

When I copy the formula down column B, cells B4 and B5 contain £0 as there are no values in B21 and C21. Cell B6 contains the value in D21, B7 is then £0, B8 contains the value in F21, B9 is £0, B10 contains the value in H21, B11 is £0 and so it continues.

It is probably something simple but I can't work it out and desperately need more help. I don't know if it makes it any easier but I could have the values appearing along a row instead of down a column and at least then I would be working in the same direction so instead of going down column B I could go along row 3. I've tried both ways and no luck so far.

Thanks for your help so far

Shirley Munro