Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old February 2nd 06, 12:23 PM posted to microsoft.public.excel.misc
Shirley Munro
 
Posts: n/a
Default 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


  #2   Report Post  
Old February 2nd 06, 04:30 PM posted to microsoft.public.excel.misc
wAyne
 
Posts: n/a
Default 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


  #3   Report Post  
Old February 2nd 06, 05:02 PM posted to microsoft.public.excel.misc
Shirley Munro
 
Posts: n/a
Default 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

  #4   Report Post  
Old February 2nd 06, 06:04 PM posted to microsoft.public.excel.misc
David Biddulph
 
Posts: n/a
Default Copying a Formula which contains a link to another worksheet

"Shirley Munro"
wrote in message
news:[email protected] 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


  #5   Report Post  
Old February 3rd 06, 10:06 AM posted to microsoft.public.excel.misc
Shirley Munro
 
Posts: n/a
Default 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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Formula Problem - interrupted by #VALUE! in other cells!? Ted Excel Worksheet Functions 17 November 25th 05 06:18 PM
Copying Formula Neil_Pattison Excel Discussion (Misc queries) 6 October 3rd 05 02:50 PM
Updating formula with link to another worksheet using vlookup Matt Links and Linking in Excel 3 August 12th 05 01:04 PM
Question when Copying an Existing Worksheet LL Excel Worksheet Functions 0 June 13th 05 03:17 PM
How do link to a remote worksheet using the path value in a field? Michael T. Links and Linking in Excel 3 December 11th 04 09:45 AM


All times are GMT +1. The time now is 04:40 PM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017