Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
JR573PUTT
 
Posts: n/a
Default Copy Formula That References Another Sheet


How can I copy a formula that references another sheet to a new file"

file name: sales
sumif(sheet1 a:a)

When I copy this formula to a new file the formula is:
sumif(c:/mydocs.sales 'sheet1! a:a)

I just want the formula to be:
sumif(sheet1 a:a)

Is this possible?


--
JR573PUTT
------------------------------------------------------------------------
JR573PUTT's Profile: http://www.excelforum.com/member.php...o&userid=31587
View this thread: http://www.excelforum.com/showthread...hreadid=513310

  #2   Report Post  
Posted to microsoft.public.excel.misc
Dave Peterson
 
Posts: n/a
Default Copy Formula That References Another Sheet

Copy from the formula bar and paste into the formula bar.

Or change the formula to text
select your range of formulas (more than one cell)
edit replace
what: = (equal sign)
with: $$$$$
replace all

copy and paste

and then change the strings back to formulas (edit|replace--in both worksheets)

JR573PUTT wrote:

How can I copy a formula that references another sheet to a new file"

file name: sales
sumif(sheet1 a:a)

When I copy this formula to a new file the formula is:
sumif(c:/mydocs.sales 'sheet1! a:a)

I just want the formula to be:
sumif(sheet1 a:a)

Is this possible?

--
JR573PUTT
------------------------------------------------------------------------
JR573PUTT's Profile: http://www.excelforum.com/member.php...o&userid=31587
View this thread: http://www.excelforum.com/showthread...hreadid=513310


--

Dave Peterson
  #3   Report Post  
Posted to microsoft.public.excel.misc
vezerid
 
Posts: n/a
Default Copy Formula That References Another Sheet

Double-click the cell to edit. Copy the formula from the formula bar,
i.e. copy the text of the formula and paste it.

HTH
Kostis Vezerides

  #4   Report Post  
Posted to microsoft.public.excel.misc
JR573PUTT
 
Posts: n/a
Default Copy Formula That References Another Sheet


Thanks that worked, but not for an array formula where I commit the
formula by hitting ctrl shift enter.

Is there a way to copy an array formula that references a sheet?


--
JR573PUTT
------------------------------------------------------------------------
JR573PUTT's Profile: http://www.excelforum.com/member.php...o&userid=31587
View this thread: http://www.excelforum.com/showthread...hreadid=513310

  #5   Report Post  
Posted to microsoft.public.excel.misc
JR573PUTT
 
Posts: n/a
Default Copy Formula That References Another Sheet


Dave Peterson Wrote:
Copy from the formula bar and paste into the formula bar.

Or change the formula to text
select your range of formulas (more than one cell)
edit replace
what: = (equal sign)
with: $$$$$
replace all

copy and paste

and then change the strings back to formulas (edit|replace--in both
worksheets)

JR573PUTT wrote:

How can I copy a formula that references another sheet to a new

file"

file name: sales
sumif(sheet1 a:a)

When I copy this formula to a new file the formula is:
sumif(c:/mydocs.sales 'sheet1! a:a)

I just want the formula to be:
sumif(sheet1 a:a)

Is this possible?

--
JR573PUTT

------------------------------------------------------------------------
JR573PUTT's Profile:

http://www.excelforum.com/member.php...o&userid=31587
View this thread:

http://www.excelforum.com/showthread...hreadid=513310

--

Dave Peterson



What about an array formular?


--
JR573PUTT
------------------------------------------------------------------------
JR573PUTT's Profile: http://www.excelforum.com/member.php...o&userid=31587
View this thread: http://www.excelforum.com/showthread...hreadid=513310



  #6   Report Post  
Posted to microsoft.public.excel.misc
Dave Peterson
 
Posts: n/a
Default Copy Formula That References Another Sheet

You could use code...

Option Explicit
Sub testme()

Dim FromCell As Range
Dim ToCell As Range

Set FromCell = Workbooks("book2.xls").Worksheets("sheet1").Range( "a1")
Set ToCell = Workbooks("Book1.xls").Worksheets("sheet1").Range( "a1")

If FromCell.HasFormula Then
If FromCell.HasArray Then
ToCell.FormulaArray = FromCell.FormulaArray
Else
ToCell.Formula = FromCell.Formula
End If
End If
End Sub




JR573PUTT wrote:

Dave Peterson Wrote:
Copy from the formula bar and paste into the formula bar.

Or change the formula to text
select your range of formulas (more than one cell)
edit replace
what: = (equal sign)
with: $$$$$
replace all

copy and paste

and then change the strings back to formulas (edit|replace--in both
worksheets)

JR573PUTT wrote:

How can I copy a formula that references another sheet to a new

file"

file name: sales
sumif(sheet1 a:a)

When I copy this formula to a new file the formula is:
sumif(c:/mydocs.sales 'sheet1! a:a)

I just want the formula to be:
sumif(sheet1 a:a)

Is this possible?

--
JR573PUTT

------------------------------------------------------------------------
JR573PUTT's Profile:

http://www.excelforum.com/member.php...o&userid=31587
View this thread:

http://www.excelforum.com/showthread...hreadid=513310

--

Dave Peterson


What about an array formular?

--
JR573PUTT
------------------------------------------------------------------------
JR573PUTT's Profile: http://www.excelforum.com/member.php...o&userid=31587
View this thread: http://www.excelforum.com/showthread...hreadid=513310


--

Dave Peterson
  #7   Report Post  
Posted to microsoft.public.excel.misc
JR573PUTT
 
Posts: n/a
Default Copy Formula That References Another Sheet


How do I use this code? Not familiar with code..............


--
JR573PUTT
------------------------------------------------------------------------
JR573PUTT's Profile: http://www.excelforum.com/member.php...o&userid=31587
View this thread: http://www.excelforum.com/showthread...hreadid=513310

  #8   Report Post  
Posted to microsoft.public.excel.misc
Dave Peterson
 
Posts: n/a
Default Copy Formula That References Another Sheet

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

JR573PUTT wrote:

How do I use this code? Not familiar with code..............

--
JR573PUTT
------------------------------------------------------------------------
JR573PUTT's Profile: http://www.excelforum.com/member.php...o&userid=31587
View this thread: http://www.excelforum.com/showthread...hreadid=513310


--

Dave Peterson
  #9   Report Post  
Posted to microsoft.public.excel.misc
JR573PUTT
 
Posts: n/a
Default Copy Formula That References Another Sheet


Code is Macro, yes I know how to record and run a macro, thanks for the
answer.


--
JR573PUTT
------------------------------------------------------------------------
JR573PUTT's Profile: http://www.excelforum.com/member.php...o&userid=31587
View this thread: http://www.excelforum.com/showthread...hreadid=513310

  #10   Report Post  
Posted to microsoft.public.excel.misc
JR573PUTT
 
Posts: n/a
Default Copy Formula That References Another Sheet


Dave Peterson Wrote:
If you're new to macros, you may want to read David McRitchie's intro
at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

JR573PUTT wrote:

How do I use this code? Not familiar with code..............

--
JR573PUTT

------------------------------------------------------------------------
JR573PUTT's Profile:

http://www.excelforum.com/member.php...o&userid=31587
View this thread:

http://www.excelforum.com/showthread...hreadid=513310

--

Dave Peterson


I figured out a better way:

Highlight source worksheet column,
click copy
go to recipient worksheet and paste
Go to Edit Links, change source worksheet to recipient worksheet,
click ok.

Works great...............thanks again for your help


--
JR573PUTT
------------------------------------------------------------------------
JR573PUTT's Profile: http://www.excelforum.com/member.php...o&userid=31587
View this thread: http://www.excelforum.com/showthread...hreadid=513310

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
copy cells with formula without references macsworks Excel Worksheet Functions 1 November 30th 05 10:04 PM
Formula to copy rng of cells where (value is met) to anther sheet Exit Advantage Excel Worksheet Functions 3 November 12th 05 03:59 AM
Formula checking multiple worksheets sonic-the-mouse Excel Worksheet Functions 2 June 5th 05 03:28 AM
Does excel recognise names rather than cells? Sue Excel Worksheet Functions 9 May 22nd 05 04:51 AM
Copy formula...sheet 2 sheet Mick New Users to Excel 0 January 26th 05 02:58 AM


All times are GMT +1. The time now is 11:46 PM.

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

About Us

"It's about Microsoft Excel"