software

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

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: www.excelforum.com/member.php...oamp;userid=31587
View this thread: www.excelforum.com/showthread...hreadid=513310Copy 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:
gt;
gt; How can I copy a formula that references another sheet to a new filequot;
gt;
gt; file name: sales
gt; sumif(sheet1 a:a)
gt;
gt; When I copy this formula to a new file the formula is:
gt; sumif(c:/mydocs.sales 'sheet1! a:a)
gt;
gt; I just want the formula to be:
gt; sumif(sheet1 a:a)
gt;
gt; Is this possible?
gt;
gt; --
gt; JR573PUTT
gt; ------------------------------------------------------------------------
gt; JR573PUTT's Profile: www.excelforum.com/member.php...oamp;userid=31587
gt; View this thread: www.excelforum.com/showthread...hreadid=513310

--

Dave Peterson

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
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: www.excelforum.com/member.php...oamp;userid=31587
View this thread: www.excelforum.com/showthread...hreadid=513310
Dave Peterson Wrote:
gt; Copy from the formula bar and paste into the formula bar.
gt;
gt; Or change the formula to text
gt; select your range of formulas (more than one cell)
gt; edit replace
gt; what: = (equal sign)
gt; with: $$$$$
gt; replace all
gt;
gt; copy and paste
gt;
gt; and then change the strings back to formulas (edit|replace--in both
gt; worksheets)
gt;
gt; JR573PUTT wrote:
gt; gt;
gt; gt; How can I copy a formula that references another sheet to a new
gt; filequot;
gt; gt;
gt; gt; file name: sales
gt; gt; sumif(sheet1 a:a)
gt; gt;
gt; gt; When I copy this formula to a new file the formula is:
gt; gt; sumif(c:/mydocs.sales 'sheet1! a:a)
gt; gt;
gt; gt; I just want the formula to be:
gt; gt; sumif(sheet1 a:a)
gt; gt;
gt; gt; Is this possible?
gt; gt;
gt; gt; --
gt; gt; JR573PUTT
gt; gt;
gt; ------------------------------------------------------------------------
gt; gt; JR573PUTT's Profile:
gt; www.excelforum.com/member.php...oamp;userid=31587
gt; gt; View this thread:
gt; www.excelforum.com/showthread...hreadid=513310
gt;
gt; --
gt;
gt; Dave PetersonWhat about an array formular?--
JR573PUTT
------------------------------------------------------------------------
JR573PUTT's Profile: www.excelforum.com/member.php...oamp;userid=31587
View this thread: www.excelforum.com/showthread...hreadid=513310You could use code...

Option Explicit
Sub testme()

Dim FromCell As Range
Dim ToCell As Range

Set FromCell = Workbooks(quot;book2.xlsquot;).Worksheets(quot;sheet1quot;).Range( quot;a1quot;)
Set ToCell = Workbooks(quot;Book1.xlsquot;).Worksheets(quot;sheet1quot;).Range( quot;a1quot;)

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:
gt;
gt; Dave Peterson Wrote:
gt; gt; Copy from the formula bar and paste into the formula bar.
gt; gt;
gt; gt; Or change the formula to text
gt; gt; select your range of formulas (more than one cell)
gt; gt; edit replace
gt; gt; what: = (equal sign)
gt; gt; with: $$$$$
gt; gt; replace all
gt; gt;
gt; gt; copy and paste
gt; gt;
gt; gt; and then change the strings back to formulas (edit|replace--in both
gt; gt; worksheets)
gt; gt;
gt; gt; JR573PUTT wrote:
gt; gt; gt;
gt; gt; gt; How can I copy a formula that references another sheet to a new
gt; gt; filequot;
gt; gt; gt;
gt; gt; gt; file name: sales
gt; gt; gt; sumif(sheet1 a:a)
gt; gt; gt;
gt; gt; gt; When I copy this formula to a new file the formula is:
gt; gt; gt; sumif(c:/mydocs.sales 'sheet1! a:a)
gt; gt; gt;
gt; gt; gt; I just want the formula to be:
gt; gt; gt; sumif(sheet1 a:a)
gt; gt; gt;
gt; gt; gt; Is this possible?
gt; gt; gt;
gt; gt; gt; --
gt; gt; gt; JR573PUTT
gt; gt; gt;
gt; gt; ------------------------------------------------------------------------
gt; gt; gt; JR573PUTT's Profile:
gt; gt; www.excelforum.com/member.php...oamp;userid=31587
gt; gt; gt; View this thread:
gt; gt; www.excelforum.com/showthread...hreadid=513310
gt; gt;
gt; gt; --
gt; gt;
gt; gt; Dave Peterson
gt;
gt; What about an array formular?
gt;
gt; --
gt; JR573PUTT
gt; ------------------------------------------------------------------------
gt; JR573PUTT's Profile: www.excelforum.com/member.php...oamp;userid=31587
gt; View this thread: www.excelforum.com/showthread...hreadid=513310

--

Dave Peterson


How do I use this code? Not familiar with code..............--
JR573PUTT
------------------------------------------------------------------------
JR573PUTT's Profile: www.excelforum.com/member.php...oamp;userid=31587
View this thread: www.excelforum.com/showthread...hreadid=513310If you're new to macros, you may want to read David McRitchie's intro at:
www.mvps.org/dmcritchie/excel/getstarted.htm

JR573PUTT wrote:
gt;
gt; How do I use this code? Not familiar with code..............
gt;
gt; --
gt; JR573PUTT
gt; ------------------------------------------------------------------------
gt; JR573PUTT's Profile: www.excelforum.com/member.php...oamp;userid=31587
gt; View this thread: www.excelforum.com/showthread...hreadid=513310

--

Dave Peterson


Code is Macro, yes I know how to record and run a macro, thanks for the
answer.--
JR573PUTT
------------------------------------------------------------------------
JR573PUTT's Profile: www.excelforum.com/member.php...oamp;userid=31587
View this thread: www.excelforum.com/showthread...hreadid=513310
Dave Peterson Wrote:
gt; If you're new to macros, you may want to read David McRitchie's intro
gt; at:
gt; www.mvps.org/dmcritchie/excel/getstarted.htm
gt;
gt; JR573PUTT wrote:
gt; gt;
gt; gt; How do I use this code? Not familiar with code..............
gt; gt;
gt; gt; --
gt; gt; JR573PUTT
gt; gt;
gt; ------------------------------------------------------------------------
gt; gt; JR573PUTT's Profile:
gt; www.excelforum.com/member.php...oamp;userid=31587
gt; gt; View this thread:
gt; www.excelforum.com/showthread...hreadid=513310
gt;
gt; --
gt;
gt; 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: www.excelforum.com/member.php...oamp;userid=31587
View this thread: www.excelforum.com/showthread...hreadid=513310