| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| copy cells with formula without references | Excel Worksheet Functions | |||
| Formula to copy rng of cells where (value is met) to anther sheet | Excel Worksheet Functions | |||
| Formula checking multiple worksheets | Excel Worksheet Functions | |||
| Does excel recognise names rather than cells? | Excel Worksheet Functions | |||
| Copy formula...sheet 2 sheet | New Users to Excel | |||
Linear Mode
