ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   Copying a Formula which contains a link to another worksheet (https://www.excelbanter.com/excel-discussion-misc-queries/68985-copying-formula-contains-link-another-worksheet.html)

Shirley Munro

Copying a Formula which contains a link to another worksheet
 

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


wAyne

Copying a Formula which contains a link to another worksheet
 
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

Copying a Formula which contains a link to another worksheet
 

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


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


David Biddulph

Copying a Formula which contains a link to another worksheet
 
"Shirley Munro"
wrote in message
news:Shirley.Munro.22lnfm_1138879501.0748@excelfor um-nospam.com...

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

Copying a Formula which contains a link to another worksheet
 

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


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



All times are GMT +1. The time now is 01:35 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com