Copy a sheet contained formulas from one workbook to another and remove file links

Hello,

in our company we use a special excel file with more sheets. Every user 
copies first this file from the server to his own computer and then he/she 
can customize the workbook. Usually the user only makes his own new sheets 
where there are formulas which contain cells from the original sheets.

Occasionally a new release of the original excel file appears and the user 
must make a new copy of this file. Commonly the user wants to have in the 
new file all of his own created sheets from the first workbook. If he 
normally copies the sheets from the first workbook to the new one, the 
formulas to his regret contain the links to the cells (sheets) of the first 
workbook instead to the cells (sheets) of the new one.

For better imagination - the user wants in his Sheet2 of a Book2 a formula 
like

=Sheet1!A1+Sheet1!A2  ,

he gets but a formula like

=[Book1]Sheet1!A1+[Book1]Sheet1!A2      .

One solution is that he can replace all  [Book1]  with an empty string. Is 
there any other "paste special" way of copying?

Ivan


0
ivan5185 (9)
6/11/2009 12:30:32 PM
excel 39879 articles. 2 followers. Follow

3 Replies
557 Views

Similar Articles

[PageSpeed] 0

You could use
Edit|links|change links
(xl2003 menus)

or the edit|replace that you suggested.

Another option would be to create a macro that provides the user with a choice
of what worksheets and then does the copy (and either avoids the problem or
fixes the problem).



Ivan wrote:
> 
> Hello,
> 
> in our company we use a special excel file with more sheets. Every user
> copies first this file from the server to his own computer and then he/she
> can customize the workbook. Usually the user only makes his own new sheets
> where there are formulas which contain cells from the original sheets.
> 
> Occasionally a new release of the original excel file appears and the user
> must make a new copy of this file. Commonly the user wants to have in the
> new file all of his own created sheets from the first workbook. If he
> normally copies the sheets from the first workbook to the new one, the
> formulas to his regret contain the links to the cells (sheets) of the first
> workbook instead to the cells (sheets) of the new one.
> 
> For better imagination - the user wants in his Sheet2 of a Book2 a formula
> like
> 
> =Sheet1!A1+Sheet1!A2  ,
> 
> he gets but a formula like
> 
> =[Book1]Sheet1!A1+[Book1]Sheet1!A2      .
> 
> One solution is that he can replace all  [Book1]  with an empty string. Is
> there any other "paste special" way of copying?
> 
> Ivan

-- 

Dave Peterson
0
petersod (12005)
6/11/2009 1:36:41 PM
Hi,

In Excel 2007 you could choose Office Button, Prepare, Edit Links to Files, 
select the file with the links and choose Change Source, navigate to your 
current file select it and click Open.

-- 
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Ivan" wrote:

> Hello,
> 
> in our company we use a special excel file with more sheets. Every user 
> copies first this file from the server to his own computer and then he/she 
> can customize the workbook. Usually the user only makes his own new sheets 
> where there are formulas which contain cells from the original sheets.
> 
> Occasionally a new release of the original excel file appears and the user 
> must make a new copy of this file. Commonly the user wants to have in the 
> new file all of his own created sheets from the first workbook. If he 
> normally copies the sheets from the first workbook to the new one, the 
> formulas to his regret contain the links to the cells (sheets) of the first 
> workbook instead to the cells (sheets) of the new one.
> 
> For better imagination - the user wants in his Sheet2 of a Book2 a formula 
> like
> 
> =Sheet1!A1+Sheet1!A2  ,
> 
> he gets but a formula like
> 
> =[Book1]Sheet1!A1+[Book1]Sheet1!A2      .
> 
> One solution is that he can replace all  [Book1]  with an empty string. Is 
> there any other "paste special" way of copying?
> 
> Ivan
> 
> 
> 
0
6/11/2009 4:46:01 PM
Thank you Shane. (We are using Excel 2007)  I knew there must be something 
like this.

Ivan

"Shane Devenshire" <ShaneDevenshire@discussions.microsoft.com> wrote in 
message news:3615F57C-A9A5-4036-805B-87023BDC0445@microsoft.com...
> Hi,
>
> In Excel 2007 you could choose Office Button, Prepare, Edit Links to 
> Files,
> select the file with the links and choose Change Source, navigate to your
> current file select it and click Open.
>
> -- 
> If this helps, please click the Yes button.
>
> Cheers,
> Shane Devenshire
>
>
> "Ivan" wrote:
>
>> Hello,
>>
>> in our company we use a special excel file with more sheets. Every user
>> copies first this file from the server to his own computer and then 
>> he/she
>> can customize the workbook. Usually the user only makes his own new 
>> sheets
>> where there are formulas which contain cells from the original sheets.
>>
>> Occasionally a new release of the original excel file appears and the 
>> user
>> must make a new copy of this file. Commonly the user wants to have in the
>> new file all of his own created sheets from the first workbook. If he
>> normally copies the sheets from the first workbook to the new one, the
>> formulas to his regret contain the links to the cells (sheets) of the 
>> first
>> workbook instead to the cells (sheets) of the new one.
>>
>> For better imagination - the user wants in his Sheet2 of a Book2 a 
>> formula
>> like
>>
>> =Sheet1!A1+Sheet1!A2  ,
>>
>> he gets but a formula like
>>
>> =[Book1]Sheet1!A1+[Book1]Sheet1!A2      .
>>
>> One solution is that he can replace all  [Book1]  with an empty string. 
>> Is
>> there any other "paste special" way of copying?
>>
>> Ivan
>>
>>
>> 


0
ivan5185 (9)
6/12/2009 6:00:27 AM
Reply:

Similar Artilces:

Change paths to linked external data
Hello. I have a database that has 3 external links in it: 1 is to a table in another Access Database. One is to an excel spread sheet. And, the third is to a .csv file. Is there a way in VBA to change the paths to these? Basically, i want to set up a form where the user can specify the new paths in text boxes if any of these files are moved. Anybody help me out? (Access 2007) cyb3rwolf wrote: >Hello. I have a database that has 3 external links in it: 1 is to a table >in another Access Database. One is to an excel spread sheet. And, the third >is to a .csv...

Hiding / Unhiding Sheets
I have created macros to hide and unhide sheets when clicking on a button example - Sheets("Premium Labor Utilization").Visible = Not Sheets("Premium Labor Utilization").Visible I am also assigning goups of sheets to a summary button example Application.ScreenUpdating = False Sheets("Premium Labor Utilization").Visible = Not Sheets("Premium Labor Utilization").Visible Sheets("FMLA Compliance").Visible = Not Sheets("FMLA Compliance").Visible Sheets("FLSA Compliance").Visible = Not Sheets("FLSA ...

trouble with copying a cell to other cells.
Info adds +1 (so 3oo goes to 301) and in the same function, 2 stays a 2 when copied to another cell. How do I mark what changes and what doesn't? I found the info posted on a different post. by BenjieLop for Copying a formula 9/20/2004 I want to copy a formula from one cell to several others, it is a basic formula =c5*d12 when i copy and paste the formula changes..( =c6*d13 and so on...) I want the second part d12 to change but i want to anchor the first part somehow so the c5 is constant in every formula c5*d13 c5*d14 etc.. The formula =$C$5*D12 will do the trick ...

Copy Public Folders
I am trying to make a copy of some Public folders on an Exchange 5.5 server using Outlook 2000. It starts copying and stops, saying an unknown error has occurred. I am using the administrator mailbox which is the Service Administrator in Exchange. I can view, open, and make changes to the items in the Public Folders. Any ideas? "David Hurst" <11dhurst@123vecelliogroup.com> wrote in news:ur2q9bY8DHA.2952@TK2MSFTNGP09.phx.gbl: > I am trying to make a copy of some Public folders on an Exchange 5.5 > server using Outlook 2000. > It starts copying and stops, saying an...

Bulk Import Wizard for Leads Issue
Hi, I am trying to import Lead records using a CSV file containing around 3,000 records. I have tested with around 20 records (taken from the larger file) and it imports fine but when I select the the 3,000 record file for import the wizard gives an error message somthing like (sorry don't have exact wording) 'Source Import file is corrupt'. Are there any known issues with CSV files such as the following : 1) Special characters such as an asterix not handled 2) Email and Website type fields must all be correctly formatted? 3) File is too large Thanks for your help in adva...

How to transpose in excel file
Hi I would like to make a transpose like this? From House Defect How much #1 Broken Sinks 1000 #1 Bad Paint 2000 #1 Bad Lighting 1000 #2 Broken Sinks 0 #2 Bad Paint 2000 #2 Bad Lighting 100 and Transpose into House Broken Sinks Bad Paint Bad Lighting #1 1000 2000 1000 #2 0 2000 100 A Pivot Table will do that and more. Set House as row field, Defect as column field and How much as data. HTH. Best wishes Harald "vilfood" <vilfood@d...

MBF file won't open...am I hosed???
In my previous computer I had a flaky hard drive. While it still worked I backed up all my important files to CD's and DVD's. Since I dutifully backed up my MNY file (to two locations) each time I used my Money 2001 I didn't think to copy my MNY file I merely copied the MBF files from those locations. Once, I had to use that an MBF file as my MNY file was corrupt and it worked flawlessly. I saw no reason why it shouldn't do so again. Plus I was in panic mode to get all the data possible from the dying HD. Now that I have a new computer I installed my copy of MM 2001 and wh...

excel file can't be opened #2
hi,I'm cho I have a recent excel file (about 1 month),but since two days ago,the file can't be opened completely or always not responding. What should I do so that I can use this important file. Any help would be appreciated regards, cho ...

Outlook 2003 attach File Browse Window Stops Responding
In outlook 2003 when I try to attach a file and the browse window pops open to browse for the file I want to attach the window will stop responding and I have to force quit the outlook. Any Ideas? do you have mapped network drives? how long do you wait before quitting outlook? -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Coauthor, OneNote 2003 for Windows (Visual QuickStart Guide) Author, Google and Other Search Engines (Visual QuickStart Guide) Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipsti...

Can anyone send me the Microsoft.Crm.Platform.Proxy.dll and Microsoft.Crm.Platform.Types.dll files ?
Hi, I'm stucked without those files, and I don't have the instalation disk. Can anyone send me those files ? Thanks in advance, Eddie. You can download the program from Microsoft. http://www.microsoft.com/office/onenote/prodinfo/trial.mspx#EEAA Sorry-wrong group Eddie, email me and i will send them over -- John O'Donnell Microsoft CRM MVP http://www.mscrmfaq.us "Eddie" <Eddie@discussions.microsoft.com> wrote in message news:epJ2yF3NFHA.3076@tk2msftngp13.phx.gbl... > Hi, > > I'm stucked without those files...

Paste Several Values into one Cell
Hi all. I have data in several cells (I2:GJ2) on one row. I want to copy all of this data into one cell as it appears now (in a particular sequence). Is this possible? Thx in advance. Not sure if this is what you want but to concatenate data from several cells into 1 cell, use formula =Concatenate(I2," ",H2," ",G2) just add more cell references with commas and " " to give a space between each cell value for all the cells you want. "sgarrett" <anonymous@discussions.microsoft.com> wrote in message news:110F5183-D1CE-4C5A-A7F8-90D01E500FBE@micr...

A Linking Problem
Assume I have a column of numbers, in a file named Source. (range is B2:B10). These cells are linked to a column in a file named Destination(range is E2:E10). If I add a new row 5 to the source, is there a way I can get the previously linked cells in Destination to continue to display the correct values. After adding the new row 5 in source, rows in Destination below row 5 display values from rows below (in the Source file). I hope this is not too confusing a question (I am confused). If it of importance, the linking was done using Paste Link in the Paste Special dialog. Thanks. Hi PA Yo...

Can't restor backup file, "Please insert the next backup disk.....
I have money 2005 backup file (.mbf) and my original file has gotten corrupted somehow and can't repair it (computer hung and had to be restarted). Whenever I try to restor the backup file, I get the following message, Window Title: Money Multiple Disk Backup Message: Please insert the next backup disk. (The disk containing file "".) I was backing up on a different connected single harddrive and there is only one file. The file is not in multiple pieces and on different drives. When I click OK, it doesn't do anything and cancel just stops the process. I even down...

Protecting a sheet (yet allowing buttons/macros to work)?
I'm new to the button/macro feature of Excel (even though I've been assigning macros to _toolbars_ for years <g>!) So, I don't even know if what I need to do can be done: Ideally, it would be nice if I could protect all the sheets or even better the workbook in one fell swoop, so that no-one could accidentally delete the buttons YET that would still allow the macros to work. I've tried everything I know and the best I seem to be able to do, yes, protects the buttons but I the macros stop working and display a ""Run-time error '1004'". T...

Password Protection on Workbooks
Hi, I set a password on a file I needed for university and now cannot remember the password. I have tried all the usual things I would set it to but no luck. I desperately need to get into the file. I have tried several different programs found in Google and have failed miserably. Is this mission impossible? Please help? -- xirokx ------------------------------------------------------------------------ xirokx's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=25533 View this thread: http://www.excelforum.com/showthread.php?threadid=389696 Take a look here...

Excel 2000 fail to open workbook
Hi, I have one Excel template. If we open it on Excel 2000, the Excel will hang, and the process occupy cpu 95% ~98%. But we can open this tempalte in Excel 2003 normally, and if we save this tempalte in Excel 2003, and then open it in Excel 2000, it work normally too. Excel 2000 (9.0.3821 SR-1) Excel 2003 (11.8231.8221) sp3 Would you help us to find the reason for this issue? Thanks, Nancy Hi Nancy, Perhaps there was some (probably minor) corruption/inconsistency in the workbook that Excel 2003 could fix but Excel 2000 couldn't. -- Cheers macropod [Microsoft MVP - Word] ...

How do you check out a file from a link
I have received a link to an excel file stored in a SharePoint library. When I open the file the option to 'check out the file' does not appear at the top of my screen. This function does work for a word document in the same library. Please can you advise. Thanks With your browser, go to the Library where the document is located and check out from there. --rms www.rmschneider.com On 16/04/10 15:44, Tracey T wrote: > I have received a link to an excel file stored in a SharePoint library. When > I open the file the option to 'check out the fi...

Excel prints some sheets very small
When I try to print multiple sheets of the same workbook at the same time, one of them (a different one each time) prints very small (about an inch tall) in the upper left hand corner. I am printing from a network file but I don't know if this has anything to do with it or not. Please HELP!!! ...

Need Help With A Formula Please
Hello and thank you in advance for your help! I have a Name on a column. The same name is listed multiple times on another sheet. However, one of the entries has the word DSL on the colmun next to it and on the next colum a value. I want to count those values. For example: On sheet 1 I have: Mike on A2 On sheet 2 i have: A2= Mike B2= Sales c2= 5 A3= Mike B3= DSL C3= 8 A4= Mike B4= Parts C4= 10 I want a formula of sheet1 that will return a value of 8 for Mike by matching the word Mike and DSL on sheet 2. Please helppppp!!!! Thanks!! -- EVRPAGING -------------------------------...

how do I insert rows without changing formula
In a cashflow worksheet how can I add rows without having to re-do all the proceeding formulas. use absolute references e.g. =$A$5 -- HTH RP (remove nothere from the email address if mailing direct) "tinka" <tinka@discussions.microsoft.com> wrote in message news:4ED4569E-0FD6-469B-82B1-F30139B1A280@microsoft.com... > In a cashflow worksheet how can I add rows without having to re-do all the > proceeding formulas. ...

Merging Workbooks #3
Hi all I have a workbook ( Master.xls ) which contains a list of parts i column A I reveive a new list of parts ( NewDoc.Xls ) once a month which ma contain some of the parts on Master.xls and will contain some ne ones. I am looking for a way to add the Newdoc.xls to Master.xls but not t include any parts that are duplicated, but to highlight them instead. Not sure this is possible ? Any ideas Thanks Davi -- DB10 ----------------------------------------------------------------------- DB100's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=917 View this thr...

Copy contacts
Is it possible to somehow copy contacts from CRM Contacts folder to Outlook contacts folder and vice versa? Hi Frenki, I wouldn't recommend 'copying' contacts from CRM to Outlook and vice versa as this can cause duplication. A good idea to make contacts available in both places is by clicking the 'Track in CRM' button inside each outlook Contact and as for the contacts in CRM, they will be created in Outlook during Synchronization. HTH, Niths "Frenki" wrote: > Is it possible to somehow copy contacts from CRM Contacts folder to Outlook > contacts ...

auto file path update when excel sheet moved to another directory.
Hello, I've been working on a huge excel file for a while and I'm trying to make it easier to use. My problem is that when it is moved from the template folder to a working folder the formulas referencing a file in the template folder have their path changed in the formula and have to have the links updated. Since the file is protected it's impossible for the users to chance the source. It's wierd because it will still pull information, but if information is changed in the external data file the updated doesn't get ported over. I need some like the $ command to ...

Re: merging a pub file into a pub document?
GbH wrote: > I'm sure I've seen this somewhere in here or a similar newsgroup. > Someone clever has written a script that will import a publisher > document into an existing/open publisher document. > Please can somebody point me at it. > > -- Also why did my post seem to disappear off the server before I could read it? -- -- Wisdom and experience come with age, they say, but I wish I could remember the darn question Both of your posts show -- Don Vancouver, USA up here. "GbH" <Geoff_Hannington@IEE.ORGasm> wrote in message news:u7JmyS0d...

how to convert word file into excel file
If any body know about how to convert MS Word file into MS Excel file please let me know. I am unable to do so. Help me Rizwan Wrote: > If any body know about how to convert MS Word file into MS Excel file > please > let me know. > I am unable to do so. > > Help me Hi Rizwan Try Copy and Paste -- Paul Sheppard ------------------------------------------------------------------------ Paul Sheppard's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24783 View this thread: http://www.excelforum.com/showthread.php?threadid=467752 Dear Pau...