Coping formulas to new workbook

Is there a way to copy formulas from one workbook and them paste it  to 
another workbook without it having a link in the formula to the previous 
workbook?

This is a formula I copied but all I need is the last part that said 
=INVENTORY$d$177 not the link to another workbook

='C:\Documents and Settings\Desktop\SONIC REPORTS\IDEALS\aug ideals\[XDQ 
IDEALS 2005 rev0805.xls]INVENTORY'!$D$177
-- 
Jim Salyer
Area Supervisor
Home:   505-474-4863
Cell:       505-670-4138
Fax:       505-474-4540
Email:    jims01@comcast.net 


0
jims1 (12)
7/28/2005 5:57:38 PM
excel 39879 articles. 2 followers. Follow

12 Replies
717 Views

Similar Articles

[PageSpeed] 33

I like to do this:

Select all the cells.
edit|replace
what:  = (equal sign)
with:  $$$$$= (some unique string)
replace all

Now all your formulas are text.

Copy them and paste them to the new worksheet.

And edit|replace
what: $$$$$= 
with: =
replace all

(remember to do it in the original workbook, too--or close without saving.)

===
Another option is to copy normally, but save your workbook and then 
Edit|Links|change source



big jim wrote:
> 
> Is there a way to copy formulas from one workbook and them paste it  to
> another workbook without it having a link in the formula to the previous
> workbook?
> 
> This is a formula I copied but all I need is the last part that said
> =INVENTORY$d$177 not the link to another workbook
> 
> ='C:\Documents and Settings\Desktop\SONIC REPORTS\IDEALS\aug ideals\[XDQ
> IDEALS 2005 rev0805.xls]INVENTORY'!$D$177
> --
> Jim Salyer
> Area Supervisor
> Home:   505-474-4863
> Cell:       505-670-4138
> Fax:       505-474-4540
> Email:    jims01@comcast.net

-- 

Dave Peterson
0
petersod (12005)
7/28/2005 10:23:50 PM
Here's a simpler way if it's a one-off formula (or few): Click on the
cell that contains the formula and select any part or all of the
formula displayed in the formula bar except the = sign. Select the
destination cell, click in the formula bar, type = and use the keyboard
short cut combination Ctrl+V to paste the formula text. Press Enter or
Tab to leave the cell (don't use mouse) and you're done.

0
micron (18)
7/30/2005 1:39:34 AM
If it's only one cells (or a few), you could copy from the formula bar (all the
formula, including the equal sign) and paste into the formula bar after you
select the destination cell.



Dave wrote:
> 
> Here's a simpler way if it's a one-off formula (or few): Click on the
> cell that contains the formula and select any part or all of the
> formula displayed in the formula bar except the = sign. Select the
> destination cell, click in the formula bar, type = and use the keyboard
> short cut combination Ctrl+V to paste the formula text. Press Enter or
> Tab to leave the cell (don't use mouse) and you're done.

-- 

Dave Peterson
0
petersod (12005)
7/30/2005 11:43:22 AM
Respectfully, I think you're guessing (unless your version is different
somehow). What happened in the past when I tried that is I got an extra
= sign added to the begining, then the cell reference chosen comes
after that, then the pasted text. e.g. =A5=SUM(A3+B3) where A5 is the
selected cell.

0
micron (18)
7/31/2005 5:53:08 PM
dear jim

its answer is simple
first go to that cell
and press F2 key from your keyborad
then u select you formula.... select from keyboard use... shift+arrow keys..
or other way of selection with your mouse.
then copy wiht right click.. or use shortcut from keyboard.. it is ctrl+c and
then go to destination cell. whom you paste your formula... and simply paste
them..... and enjoy.. your work..
Take Care.. GOD bless you dear...



big jim wrote:
>Is there a way to copy formulas from one workbook and them paste it  to 
>another workbook without it having a link in the formula to the previous 
>workbook?
>
>This is a formula I copied but all I need is the last part that said 
>=INVENTORY$d$177 not the link to another workbook
>
>='C:\Documents and Settings\Desktop\SONIC REPORTS\IDEALS\aug ideals\[XDQ 
>IDEALS 2005 rev0805.xls]INVENTORY'!$D$177
0
forum (466)
7/31/2005 7:33:53 PM
No, not a guess.

I've used this technique lots and lots of time.

I copy the whole expression from the formula bar.  Go to the other cell.  Click
in the formula bar (erase what's ever there, if necessary) and paste.  

Are you sure you didn't type the equal sign (or click that equal sign icon)?

Dave wrote:
> 
> Respectfully, I think you're guessing (unless your version is different
> somehow). What happened in the past when I tried that is I got an extra
> = sign added to the begining, then the cell reference chosen comes
> after that, then the pasted text. e.g. =A5=SUM(A3+B3) where A5 is the
> selected cell.

-- 

Dave Peterson
0
petersod (12005)
7/31/2005 11:29:49 PM
Ps.  I did leave out one step or two!.

1.  Select the "sending" cell
2.  Select all the formula (including the equal sign)
3.  hit ctrl-c (or rightclick|copy)
4.  Hit Escape (to leave that cell)
5.  Select the "receiving" cell
6.  Click in the formula bar
7.  ctrl-v or rightclick|paste




Dave Peterson wrote:
> 
> No, not a guess.
> 
> I've used this technique lots and lots of time.
> 
> I copy the whole expression from the formula bar.  Go to the other cell.  Click
> in the formula bar (erase what's ever there, if necessary) and paste.
> 
> Are you sure you didn't type the equal sign (or click that equal sign icon)?
> 
> Dave wrote:
> >
> > Respectfully, I think you're guessing (unless your version is different
> > somehow). What happened in the past when I tried that is I got an extra
> > = sign added to the begining, then the cell reference chosen comes
> > after that, then the pasted text. e.g. =A5=SUM(A3+B3) where A5 is the
> > selected cell.
> 
> --
> 
> Dave Peterson

-- 

Dave Peterson
0
petersod (12005)
7/31/2005 11:33:33 PM
Dave,
I do this a lot, only in step #4 i hit Enter instead of Escape
Dave (different Dave)
Dave Peterson Wrote: 
> Ps.  I did leave out one step or two!.
> 
> 1.  Select the "sending" cell
> 2.  Select all the formula (including the equal sign)
> 3.  hit ctrl-c (or rightclick|copy)
> 4.  Hit Escape (to leave that cell)
> 5.  Select the "receiving" cell
> 6.  Click in the formula bar
> 7.  ctrl-v or rightclick|paste
> 
> 
> 
> 
> Dave Peterson wrote:
> >
> > No, not a guess.
> >
> > I've used this technique lots and lots of time.
> >
> > I copy the whole expression from the formula bar.  Go to the other
> cell.  Click
> > in the formula bar (erase what's ever there, if necessary) and
> paste.
> >
> > Are you sure you didn't type the equal sign (or click that equal sign
> icon)?
> >
> > Dave wrote:
> > >
> > > Respectfully, I think you're guessing (unless your version is
> different
> > > somehow). What happened in the past when I tried that is I got an
> extra
> > > = sign added to the begining, then the cell reference chosen comes
> > > after that, then the pasted text. e.g. =A5=SUM(A3+B3) where A5 is
> the
> > > selected cell.
> >
> > --
> >
> > Dave Peterson
> 
> --
> 
> Dave Peterson


-- 
Piranha
------------------------------------------------------------------------
Piranha's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=20435
View this thread: http://www.excelforum.com/showthread.php?threadid=391043

0
8/1/2005 4:16:07 AM
If you're sure you didn't change anything, there's not too much difference (a
recalculation???).  But I figure that it's never a bad idea to use escape if you
didn't want to make a change.

Piranha wrote:
> 
> Dave,
> I do this a lot, only in step #4 i hit Enter instead of Escape
> Dave (different Dave)
> Dave Peterson Wrote:
> > Ps.  I did leave out one step or two!.
> >
> > 1.  Select the "sending" cell
> > 2.  Select all the formula (including the equal sign)
> > 3.  hit ctrl-c (or rightclick|copy)
> > 4.  Hit Escape (to leave that cell)
> > 5.  Select the "receiving" cell
> > 6.  Click in the formula bar
> > 7.  ctrl-v or rightclick|paste
> >
> >
> >
> >
> > Dave Peterson wrote:
> > >
> > > No, not a guess.
> > >
> > > I've used this technique lots and lots of time.
> > >
> > > I copy the whole expression from the formula bar.  Go to the other
> > cell.  Click
> > > in the formula bar (erase what's ever there, if necessary) and
> > paste.
> > >
> > > Are you sure you didn't type the equal sign (or click that equal sign
> > icon)?
> > >
> > > Dave wrote:
> > > >
> > > > Respectfully, I think you're guessing (unless your version is
> > different
> > > > somehow). What happened in the past when I tried that is I got an
> > extra
> > > > = sign added to the begining, then the cell reference chosen comes
> > > > after that, then the pasted text. e.g. =A5=SUM(A3+B3) where A5 is
> > the
> > > > selected cell.
> > >
> > > --
> > >
> > > Dave Peterson
> >
> > --
> >
> > Dave Peterson
> 
> --
> Piranha
> ------------------------------------------------------------------------
> Piranha's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=20435
> View this thread: http://www.excelforum.com/showthread.php?threadid=391043

-- 

Dave Peterson
0
petersod (12005)
8/1/2005 11:41:50 AM
No, I didn't click the = sign, but it does work using your revised
instruction. Hitting "Esc" drops the reference to the original cell
when pasting into the new one. Following adeel's (10th posting) method
results in =A3=SUM(C1+D1) where A3 is the destination cell for me. As
you can probably see, without hitting Esc your method still carries
over the first formula. As for your method or mine, either requires the
same number of mouse clicks and keyboard presses, so it's a matter of
preference I guess.

0
micron (18)
8/2/2005 3:39:29 AM
If you don't enter or escape after the copy, you're still editting the cell with
the original formula.

And I agree that it's a matter of preference.  I like to select the whole
formula.  I find it easier to swipe the whole formula than positioning the
cursor after the equal sign.

Dave wrote:
> 
> No, I didn't click the = sign, but it does work using your revised
> instruction. Hitting "Esc" drops the reference to the original cell
> when pasting into the new one. Following adeel's (10th posting) method
> results in =A3=SUM(C1+D1) where A3 is the destination cell for me. As
> you can probably see, without hitting Esc your method still carries
> over the first formula. As for your method or mine, either requires the
> same number of mouse clicks and keyboard presses, so it's a matter of
> preference I guess.

-- 

Dave Peterson
0
petersod (12005)
8/2/2005 12:11:11 PM
I think I like your way better!

0
micron (18)
8/3/2005 3:44:15 AM
Reply:

Similar Artilces:

Prevent new pivot table items from being selected
I have a pivot table that contains accounts and reports sales by account. Users selected the accounts they want to see and save their spreadsheet. When new accounts are added to the accounts list, users do not want to see the new accounts. They only want to see the accounts they selected from a previous session when they refresh the pivot table. Does anyone know how to prevent the new accounts from appearing in the pivot table? Thanks! Tom, Use another column: Include, with values of Yes or No, and include it in your pivot table, showing only Yes. Then when new data is added, enter ...

Error when adding new user to CRM
Hello all, I need to add 5 new users into CRM. When I complete the first form for the user information such as Last name, First name, email address, etc, then click the save button I receive an error page and the following message is logged in the Application log. MSCRM Platform Error Report: -------------------------------------------------------------------------------------------------------- Error: The statement has been terminated. Error Message: The statement has been terminated. Error Details: Details on this error have not been provided by the platform. Source File: Not avail...

SBS Exchange 2003: Create new user in AD but no exchange mailbox #3
I'm using SBS 2003. Exchange server is SP2. When I create a new user using Active Directory, it prompts me about creating a mail box. It says it's creating a mailbox, then it's done. But no mailbox appears. In fact, if I use the Exchange tasks wizard, I can create, delete, and recreate the mailbox--all say successful without an error message. And yet, no mailbox. This is even after I reboot the PC and sent email to the address. Any ideas what's going on? mike.aes@gmail.com wrote: > I'm using SBS 2003. Exchange server is SP2. > > When I create a new user ...

how do I recover charts within an excel workbook?
The workbook had many charts that were previously visible. Now, upon opening the file, the charts are no longer visible. How to I reactivate them to appear? thanks ...

If my GP Dynamics support CPA's new cheque standard
Hi; I am using GP Dynamics 8.0 in Canada, we have Payable Management module installed. How can I know if my current version support the CPA's new cheque standard? The CPA's new cheque standard will be launched in July 2007. -- Kane Kane, Support for the new cheque format has nothing to do with the Payables module. Rather, it is a function of modifications or changes to the report that produces your cheque. Lyle On Fri, 22 Sep 2006 10:06:01 -0700, Kane <Kane@discussions.microsoft.com> wrote: >Hi; > >I am using GP Dynamics 8.0 in Canada, we have Payable Managem...

Excel Formulae #4
I've been trying hard to find a formula which would enable me (from one cell) to Add several cells and Subtract the answer from another cell (subtracting items of expenditure from a starting total). I could manage this with Lotus - but Excel does not seem to behave the same. Advice would be much appreciated. Thanks, Ken. One way: Say you wanted to subtract A1, A2, A3, J4 and N5 from L6: =L6-SUM(A1:A3,J4,N5) In article <173d01c3fbd6$3df0f300$a401280a@phx.gbl>, "KenS." <anonymous@discussions.microsoft.com> wrote: > I've been trying hard ...

Does the New Worth Report have a fault
When I calculate my new worth in MS Money 2000 (old version I know) it adds my assets and liabilities instead of subtracting them. For example, if my savings, checking, investments total $10,000.00 and my liabilities total $5000.00, MS Money calculates my net worth as $15,000.00. Shouldn't my net worth be $5,000.00? Shouldn't it subtract my liabilities instead of add them? Please let me know if there is a glich in the software and how I can fix it. Also please let me know if I am financially inept. Thanks, -k In your example, the net worth should be $5,000.00. You may hav...

Shorter Formula
Can anyone shorten this formula please. Basically all it does is gives me an average of the figures in Column "W" depending on the number of times that product appears in "R" column =IF(ISERROR(SUM(SUMIF($R$5:$R$9,R62,$W$5:$W$9),SUMIF ($R$22:$R$26,R62,$W$22:$W$26),SUMIF ($R$39:$R$43,R62,$W$39:$W$43))/COUNTIF ($R$5:$R$43,R62)),0,SUM(SUMIF ($R$5:$R$9,R62,$W$5:$W$9),SUMIF ($R$22:$R$26,R62,$W$22:$W$26),SUMIF ($R$39:$R$43,R62,$W$39:$W$43))/COUNTIF($R$5:$R$43,R62)) thanks Pete I didn't try too hard to analyze your formula, just noted that your ranges and sum_ ranges...

Formula counts incorrectly
Help please. Column K contains dates and blank cells. I would like to count how many of these dates fall within a given date range. For example, in the first week in May. Column K contains these dates. 5/2/2005, 5/3/2005, 5/5/2005, 5/5/2005. I would like to count this as 4 dates within the first week of May. I created this formula but the output is 3. =SUMPRODUCT(--(ECNT!F2:F515>=DATE(2005,5,2)),--(ECNT!F2:F515<=DATE(2005,5,6)),--(ISNUMBER(ECNT!K2:K515))) I assume that it is only counting 5/5/2005 one time? I changed the dates in the formula to check a second 2nd date range (5/9 to ...

How do you replace old data with new data without creating a new .
I have existing pivot tables and I want to replace the data source worksheet with new data and the pivot tables update with the new information. I have replace the data source but the pivot tables didn't update. Hit the "Refresh Data" in the pivot table toolbar (looks like an exclamation point) -- Regards, Dave "Dena" wrote: > I have existing pivot tables and I want to replace the data source worksheet > with new data and the pivot tables update with the new information. I have > replace the data source but the pivot tables didn't update. So...

try out of new e-mail
Hi, Mike, Please let me know if you get this e-mail. Thanks, Mom Hello, I did get this email. But you also sent it to a public news group! -- http://www.ready4mainstream.ny911truth.org/index.html "Helene Meyer" wrote: > Hi, Mike, > > Please let me know if you get this e-mail. Thanks, Mom > ...

Protect formatting & formula
I need to protect the formatting & formula in each cell to avoid accidental changes during update by different users. However, if I protect them, any new row creation and deletion are disable. Is there any way resolve it? Especially, when create a row, how can all formatting and formula to create automatically for the new row? Thanks, Scott What version of Excel? "Scott" <NoSpam-Scott.Xe@GMail.com> wrote in message news:%23bsg6kCBGHA.4092@TK2MSFTNGP09.phx.gbl... >I need to protect the formatting & formula in each cell to avoid accidental >changes d...

Is there a way to automatically put the $ sign into the formula?
I want to turn this =A1 into =$A$1. How is the best way of doing this. Is there a shortcut....keyboard stroke to do this?? Hit [F4] several times and watch the results. Micky "hrb_2001" wrote: > I want to turn this =A1 into =$A$1. > How is the best way of doing this. Is there a shortcut....keyboard stroke to > do this?? > Hi, Enter =A1 then tap F4 Repeated taps of F4 scroll through the referencing options -- Mike When competing hypotheses are equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently an...

ActiveSync does not sync new mail without intervention
I have a SBS2003 with Exchange 2003 SP2. The computer is behind a Watchguard firewall. ISA is not running. OWA & OMA both work fine via https from either mobile devices or computers. From Windows Mobile 5 works fine if in cradle (I'm assuming that activesync notes & tasks syncing keeps outlook open) but if remote Initial sync - works fine Send mail - works fine read mail messages - works fine sync'd message deleted - works fine new mail - only works if mail already read Activesync shows that it has synchronized all items without error but new messages do not sync! If a ...

Link to external workbook
I want to change a formula linking to a cell in a workbook on another server so that the server name is used rather than the drive mapping. I have tried the following but I keep getting 'File Not Found': '\\Server Name:\Drive Name\...... (instead of 'G:\..... Please advise. -- R Ormerod \\Server Name:\Drive Name\ Doesn't look like a valid format... I think I'd use windows start button|run type in \\servername and point and click down the paths to find the correct workbook. If you turn "View|toolbars|address bar", then you may be able to see the path...

Excel slow to open workbook when I double-click in Windows
Hi, I am using Excel 2007 on a brand new PC (Intel Core 2 Duo; 2.53 GHz; 3.21 GB RAM; Windows XP Pro). I have installed and started using several applications already and this PC has been very fast and responsive. I am having an issue opening workbooks in Excel. If I open Excel first and then click on the Office button and choos Open, I can open up a workbook very quickly. However, when I double-click on a workbook in Windows (without having Excel open previously), it takes a *very* long time for the workbook to open. I just timed it at exactly one minute. I was wondering if anyone...

Help writing a formula
I have a huge database that I need to create numbering for each line like this example: AK-233 (2 letter state, a dash, and 1-4 digit number), but the number doesn't change each line - sometimes the number will be duplicated on multiple lines because those lines have the same title, I also need to create another cell extending that letter/number combo that will differentiate the 1st cell. So the first cell stays the same if the title is the same. The second cell adds another layer of numbering to differentiate the first cell. See example: (Row1, Col 1-4) AK-1, AK-1-1, Title1...

adding holidays to a new calendar
I have set up a new calendar that I want to share with my department and I'm trying to add legal holidays which were already on my personal calendar. Even though I'm viewing the new calendar, the holidays got added to the personal calendar. I tried deleting all the holidays and then, with both calendars open, putting the holidays back in, but again they were added only to the personal calendar. Anyone know what to do? Add them to the personal calendar and copy (or move) them to the other calendar. -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-t...

.OST full (2 GB) during POP collection = all new mail lost?
The subject says it all... Outlook 2003 in Cached Exchange Mode (non-Unicode, i.e. inheriting old 2 GB limit) on Windows XP. Was working offline, collecting two days of POP3 mail, and at the end of the collection I got the error message saying that the .ost file had reached the size limit... Very stupidly, Outlook 2003 (!!!) _deleted_ all mail from the POP3 server before making sure that the same had been stored properly, so it appears to be all lost now? Just out of curiosity, where did Outlook write this data as it was being collected? Was it being appended to the .ost file? I tried ...

Re: Could someone please help me with formulas
sorry typo http://www.eaglepi.com/formula/example.xls "No" <no@isp.com> wrote in message news:... > I created a workbook and tried to explain the best I could how everything > should be. you can download the workbook at, > http://www.eaglepi.com/formlua/example.xls > > I really appreciate everyones help on this..... > > > "CLR" <croberts@tampabay.rr.com> wrote in message > news:%235IxtFEHFHA.2936@TK2MSFTNGP15.phx.gbl... > > Maybe in cell J2 you could put the formula =H2+I2, which would give you > the > > sum of the C...

Cant see Default Public Folders on New 2003 Exchange
Hi We have recently installed a new W2003 Exchange 2003 server into our domain. The domain already had an Exchange 2000 server installed. The default public folders on the Exchange 2000 machine can be seen under ESM and by Outlook clients. We have created an additional new public folder store on the new 2003 server, however, the default Public folders on the new 2003 server cant be seen by mapi clients ie Outlook. Additionally any folder created under the Public Folder tree on the 2003 server under Folders does not appear under the First Storage Group > Public Folder Store > Public Fol...

Moving Exchange DB's to a new server (different name)
In a DR test, we moved SG's to a new server, different server name, same SG and MS name. MS mounts, can not connect to mailbox. Delete mailbox, create a new one, we can then connect. Delete this mailbox, reconnect to the original, and now we can connect. How can we accomplish this in bulk? In other words, after doing a system state restore of AD, then a system state of Exchange, restored MS file, mounted, etc. We were unable to connect to these mailboxes. I am assuming this is related to GUID's. Any thoughts? Users in AD specify a server by name. When you say that you move mailbo...

New computer, outlook locking up
Hello, My boss was having alot of problems with an old 1.5GHz HP laptop, so he asked me to find a new one for a good price. The best price range I found was the $700 dollar range (best bang for the buck). He got a Dell 2.3GHz P4 Dual core, 3GB RAM, 500GB HD, Windows 7 Home premium. Now what sucks is that after importing all his old emails into outlook, everytime it goes into send/receive, outlook freezes for a little bit. I was told earlier that my method for importing emails (just replacing the PST, rather than Data>open) was not good practice, but could that be causing these pro...

Outlook 2007-exchange issue..inbox doesn't show new mails when offline!
Hi I have outlook 2007 (Beta 2) installed and I connect to my office exchange server(2003) as well. Incoming mails from internet get routed to my local pst. Mails from exchange server keep sitting in my exchange mailbox-inbox, which I manually copy to my pst inbox (as is a known issue with Beta 2). Yesterday, for the first time I downloaded mails from home. Outlook downloaded all mails and the unread mail count increased....however, it just wouldn't show the new mails received. All it showed was my earlier mails downloaded while I was still connected to exchange server. Now I came to wo...

turning off formula bar
I can't remember or find how to turn off the formula bar in Excel 2003? Can someone help please. This is the bar that displays cell contents up top. Thanks! --Randy Starkey Tools>Options>View tab, uncheck Formula bar -- Kind regards, Niek Otten Microsoft MVP - Excel "Randy Starkey" <randy.starkeyNOSPAM@NOSPAMvictorychurch.com> wrote in message news:12kcghhahv0oif4@corp.supernews.com... |I can't remember or find how to turn off the formula bar in Excel 2003? Can | someone help please. This is the bar that displays cell contents up top. | | Thanks! | | --...