Copy formulaS between Workbooks

This might be some sort of follow up question from Ken Runge post "Cop
formula between Workbooks" from Oct-23-2003. I would like to copy som
formulas from one workbook to another without getting a reference t
the original workbook

-----------------------------------------------
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com

0
11/11/2003 2:49:03 PM
excel 39879 articles. 2 followers. Follow

3 Replies
487 Views

Similar Articles

[PageSpeed] 39

Syslock,

This happens, I think, where there are cell references to other sheets.  One
way is to copy the text of the formula (drag across the text in the formula
bar), instead of the cell.  Cell references won't adjust relatively when you
paste, though, though copying the pasted formula in the target workbook will
do so, so you can still make it work.

-- 
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"Syslock" <Syslock.wqi9b@excelforum-nospam.com> wrote in message
news:Syslock.wqi9b@excelforum-nospam.com...
>
> This might be some sort of follow up question from Ken Runge post "Copy
> formula between Workbooks" from Oct-23-2003. I would like to copy some
> formulas from one workbook to another without getting a reference to
> the original workbook.
>
>
> ------------------------------------------------
> ~~ Message posted from http://www.ExcelTip.com/
> ~~View and post usenet messages directly from http://www.ExcelForum.com/
>


0
nowhere8060 (363)
11/11/2003 3:16:07 PM
Syslock,

Don't copy the cell. Select and copy the formula text in the the
"Formula Bar" above the worksheet. At least that's what I do.

-- 
Ture Magnusson
Microsoft MVP - Excel
Karlstad, Sweden

"Syslock" <Syslock.wqi9b@excelforum-nospam.com> wrote in message
news:Syslock.wqi9b@excelforum-nospam.com...
>
> This might be some sort of follow up question from Ken Runge post "Copy
> formula between Workbooks" from Oct-23-2003. I would like to copy some
> formulas from one workbook to another without getting a reference to
> the original workbook.
>
>
> ------------------------------------------------
> ~~ Message posted from http://www.ExcelTip.com/
> ~~View and post usenet messages directly from http://www.ExcelForum.com/
>


0
Ture
11/11/2003 3:16:11 PM
One method is to select the range of formulas in book1 and Edit>Replace

what:  =
with: xx=

Do your copy over to book2 then reverse the Edit>Replace

Gord Dibben   XL2002

On Tue, 11 Nov 2003 09:49:03 -0500, Syslock
<Syslock.wqi9b@excelforum-nospam.com> wrote:

>
>This might be some sort of follow up question from Ken Runge post "Copy
>formula between Workbooks" from Oct-23-2003. I would like to copy some
>formulas from one workbook to another without getting a reference to
>the original workbook.
>
>
>------------------------------------------------
>~~ Message posted from http://www.ExcelTip.com/
>~~View and post usenet messages directly from http://www.ExcelForum.com/

0
Gord
11/11/2003 4:56:15 PM
Reply:

Similar Artilces:

Formula to average last 25 entries in a column?
Guess I have a duel question. First, I have numerous columns of data, all numerical. New entries are entered at the end of the column. Is there a formula to average just the last 25 entries in the column? (Bottom of column up) Second question pertains to the ability to "auto populate". I have a column of names. (three dozen). I would then like to auto populate the single column of names, matching the names with the average data obtained from the above. (average of last 25, 50, 100,etc entries) Data changes daily? Any help would be greatly appreciated. Hi, A...

Complicated conditional "countif" formula
Hi. I have several columns labeled "Monitoring Visit 1...Monitoring Visit 8", but interspersed between them I have columns labeled "Report Date", with each report being associated with a given Monitoring visit. In the last column, on each row, I want to count the number of cells with dates in them, which signifies if a monitoring visit has occurred or not. I can't do a normal count if, because of the extra Report columns in between, which have stay where they are. Can anyone lead me to the right formula? Thanks. =SUMPRODUCT(--(LEFT(A$1:O$1,16)="Monitoring Vi...

Naming ranges as a copy of another sheet
Dear all, There are two sheets X and Y in my workbook. On A, there are hundreds of ranges named locally (i.e. names are like "X!students"). How to write a macro to name the respective areas in Y with the same local name? For example, if X!$A$1:$B$4 is named as "X!students", then I want Y!$A$1:$B$4 to be named as "Y!students". Thanks in advance. Best Regards, Andy Something like: Option Explicit Sub testme01() Dim wksMstr As Worksheet Dim wksOther As Worksheet Dim nm As Name Dim testRng As Range Dim ExclamPos As Long ...

Table Lookup formula where 2 known values are inside array
I am using Excel. Please help, I am stuck on a non-typical table lookup I regularly use =lookup to get values where the relationship is 1:1 e.g. in a cell, I put a value, I use =lookup for that value's meaning I need to do this, but the lookup is a table, not a 1:1 column match Row 1 is a header, one of my search values Column 1 is a value I want to find the table (array) contains the other known value I need do do a enter values in 2 cells, and have a lookup return a value based on the value it looks up. eg: -- | 1 | 2 | 3 | 4 w | a | d | h | i x | b | e | i | ...

Excel Workbook- Can't open file once any changes are made
Hello everyone- I am running Windows XP with Excel 2002 (verison 10.6501.6626 with SP3 and I just completed developing a fairly large Excel workbook wit numerous worksheets and macros. If I send the file to a handful o other people, they are all able to open the workbook and make change with out getting any problems (They can change the file name itself o make changes to the cells within the workbook). Here's my problem: On my computer I can open the base version of th workbook without getting any error messages. However, if I make an changes (no matter how simple, like changing text...

Formula to print a datum in color contingent on value.
I need a formula that will change the displayed color of a value based on amount. For example: under 200=yellow, over 200=red. Also, can I add input from a third column? For example: under 200 and Column D=No, then yellow. under 200 and column D=yes, then green. Try conditional formatting for the cells Click on /scroll over cells you need to format FORMAT>CONDITIONAL FORMATTING "Shadyhosta" wrote: > I need a formula that will change the displayed color of a value based on > amount. > For example: under 200=yellow, over 200=red. > Also, can I add input from a thi...

copy selected rows to second worksheet (NOT Cut + Paste)
I am trying to create a simple tool log that also incorporates a sign in/out sheet as a second worksheet. What I want is to be able to select (not using cut + paste) several rows and by simply being selected on "tool list" worksheet, temporarly copied into "sign_in" and "sign_out" worksheets. I need the data selected from sheet 1 "tools list" to fill rows (starting at 20) of the next 2 sheets, and then end user simply prints needed sheet, for employee to sign. This allows me to select only the tools that that employee is checking in/out ...

Average Formula
I'm trying to find the average of bulletins used each Sunday. I'm keeping track of how many I produce each Sunday and how many are left. Basic table... Week Bulletin Amt. Bulletins left Week 1 100 34 Week 2 100 17 Week 3 110 20 I'm keeping track for the year (52 weeks) What kind of formula would I use? Thanks, wiersma7 You would add another column titled "Bulletins Used", calculated with: =c2-b2 Then average column D, as in: =average(d2:d4...

Copy parts of a number
If I have a cell with a digit 123,4567 - how can I copy the content of the cell to another cell with only two digits after the comma. In other words - I only want 123,45 to appear in the new cell. Is there any way to use a "round off" functionality? Well, I wonder if it is really a 'number'...? Seems to be text as written and if 123,4567 copied and pasted from the post to Excel it shows as 1,234,567. So pasted in a text formatted cell you can use =LEFT(A1,6) to return 123,45. HTH Regards, Howard "GeniE" <GeniE@discussions.microsoft.com>...

copy an e-mail
I sent an e-mail to my e-mail office address with the intention of forwarding it tomorrow morning, I then realized I forgot to edit a very important part of it. Is there a way I can edit an e-mail that was sent to me by myself from another locale then forward to others in my office ...

Create Order doesn't copy values for custom attributes
Using CRM 3.0 We have added a bunch of custom attributes to our Quote, Quote Details, Order and Order Details entities, but when I create an order from a quote (by using the Create Order toolbar button on the Quote form) the values for these custom attributes are not copied to the newly created order. When I go and look at the created order, all the custom attributes are empty. I can see them on the quote though. All standard attributes are copied as expected. I have verified that the schema names are the same in both Quote and Order (not sure if that makes a difference though). Is th...

Error copying cell containing 'IF' function and labels
I'm running Excel 97 SR-2 on Windows XP (and have found the same problem running Excel 97 on Win95). When copying a cell containing an 'IF' function that references a column label, the reference is an absolute reference rather than a relative reference. For copying other cells with formulae containing column labels but not 'IF' functions, the column label reference is a relative reference as is the obvious intent when using labels. After pasting in the destination, the absolute reference is changed to relative if the contents of the destination cell is simply copied ...

how to copy sheet with charts without link to original data
I have a worksheet with ten charts on it. I need to copy the worksheet with all its data and related charts to new sheets or files so I have a page for every day, with different data entered every day. But when I copy the worksheet with embedded charts and paste it into a new file, the charts pull their source data from the original worksheet, not the new one. The source data Value box is written as such, ='03-09-10'!$D$12 any ideas? Hi Esty, Probably wont help you, but I had similar problem, I posted few days ago. Didn't resolved it yet, but Jon suggested ...

INDEX/MATCH Formula?
Hi, I need help with a formula. My data looks like this: Column A Column B 1 3 2 7 3 5 4 8 5 1 6 7 8 9 10 And I need it to end up like this: Column A Column B 1 1 2 3 3 4 5 5 6 7 7 8 8 9 10 So, in other words, the numbers in Column B need to end up in the same row as their corresponding numbers in Column A. To do it manually will take forever as the real data is thousands of rows long. Can this be done with an INDEX/MATCH formula of some kind? I=92ve tried a few different things on my own but I clearly don=92t know what I=92m doing. Any help would be greatly appreciated. Can you plea...

How to copy custom number formats
Hello All I have a custom number format for a Excel Cell A1 which is #,##0.00 "CAD" and in A1 I now have a value 1000 CAD. I have assigned cell B1 as =A1 but an external application when the report is run populated now cell A1 as 20000 MXP and now my cell B1 says 20000 CAD and not 20000 MXP and when I check the format for B1 it still as the format as #,##0.00 "CAD" whereas now the format of my cell A1 is #,##0.00 "MXP" appears only the value and not the format is being copied. Can any of you suggest a workaround to resolve this problem. Many thanks Simon ...

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 I like to do this: Select all the cells. edit|replace what: =...

Cell Formats in formulas
I have a worksheet set up with formulas to automatically copy values from another cell as it is changed. Is there a way to include the format of the reference cell (font, color) when referencing it in a formula. Example: when I change to a different symbol font in the referenced cell, the referencing cells only change the value, not the font, so I don't end up with the correct symbols in the referencing cell. No, it's not possible to do that using formulas, only format formulas can do are number formats and only using the text function Regards, Peo Sjoblom "C. Lewis"...

cell displays formula instead of value #2
Hi, Excel 2003 SP2 I have a new blank workbook, and on a new blank worksheet I format columns A - D as Text. A1 = [This_] B1 = [is_] C1 = [text.] D1 = [=CONCATENATE(A1, B1, C1)] D1 now displays [=CONCATENATE(A1 ,B1, C1)] instead if the expected [This_is_Text.] Please help. I have hours in this :(( Thanks Jeff Higgins Jeff D1 should have been formatted as "general". Format it, re-type formula? HTH Beege "Jeff Higgins" <oohiggins@yahoo.com> wrote in message news:RFBzf.218$Cp2.147@fe03.lga... > Hi, > Excel 2003 SP2 > I have a new blank workbook, > a...

Formula referencing other file show formula not result
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel I have a formula in a cell that selects data from another spreadsheet. Both cells are text and often the formula shows in the receiving spreadsheet not the value. I have both cell formated as &quot;text&quot;. In older versions of excel, you could never have an &quot;=&quot; sign start in cell without excel always defaulting that as a formula. Now in 2008 it does not work that way any more. <br><br>Along the same line I have a formula in a cell that selects data from another spreadsheet. S...

External Workbook Link
I cannot delete a link to another xl workbook that no longer exists. Each time i go to change links to the current or any other worbook a message comes back saying, Your formula contains invalid external reference to a worksheet. I have actually inserted a new worksheet and deleted all the other sheets one at a time to try and find the cause, but it still stays linked to the other workbook. ** Posted via: http://www.ozgrid.com Excel Templates, Training, Add-ins & Business Software Galore! Free Excel Forum http://www.ozgrid.com/forum *** Try Bill Manville's excellent FindLink utility ...

printing formula definitions
I've got some fairly elaborate formula definitions and I'd like to be able to print the worksheet to show the definition and not the output of the formula. Terri, CTRL+` (that's the key to the left of "1" on a standard keyboard) toggles the view between formulae and results. You can then print to your heart's content! Cheers, Pete >-----Original Message----- >I've got some fairly elaborate formula definitions and I'd like to be able to print the worksheet to show the >definition and not the output of the formula. > Choose Tools/Option...

How do I shut off making a backup copy of a file when I save in E.
We have a shared worksheet that many people access and make changes to. Every time someone saves the file, Excel creates a backup copy of the file in the folder. How do I shut that feature off? options button on the save screen Choose File>Save As At the top right of the Save dialog box, click the Tools button Select General Options Remove the check mark from 'Always create backup' Click OK, click Save Click Yes, to replace the existing file. kboley2004 wrote: > We have a shared worksheet that many people access and make changes to. > Every time someone saves the file...

Copying Tabs in Excel
I have two tabs in an excel worksheet. Tab A has data, Tab B has graphs from the data in Tab A. When I copy and paste these two tabs into the same workbook, I get Tab A (2) and Tab B (2). The problem is that the graphs in Tab B (2) are still referencing the data in Tab B. I have to go in and change the source data from Tab B to Tab B (2). Is there a way around this procedure or do I have to go in and change the source data everytime I copy a tab with graphs? Thanks Chris If you select TabA and ctrl-click on TabB and copy them as a unit to the other workbook, does that help? Chri...

conditional formatting
I have used conditional formatting to highlight some issues in a spreadsheet. However, to distribute this, I need to remove the link to the base data used as the source file is too big. Is there a way I can copy the cell shading so that the condition is removed but the formatting remains? I am using 2003, and the spreadsheet is 7500 rows x 70 columns and running on a laptop, so any solution can't be too memory or processor intensive! Thanks for your suggestions Take a look at http://xldynamic.com/source/xld.CFConditions.html and see if it helps, although it is not si...

Physical inventory formula problems
I am having a problem with the physical inventory feature. This is the example I pull a Item Value List report with only supplier as the filter. The total on this report is as follows QTY: -5 EXTENDED COST - $10 (these are minus's) Now I go to the physical inventory and select NEW then ONLY ITEMS FROM SELECTED SUPPLIERS and I choose the same supplier I choose on the ITEM VALUE LIST I choose to update maching item and replace existing items. I am importing 10 of the item that had a -5 when I started. Now when I calulate now expected should read what my ITEM VALUE LIST started wi...