×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

How to sort formulas from a sheet?

How to sort formulas from a sheet?

How to sort formulas from a sheet?

(OP)
Greetings,

I'm currently working into a workbook which contains many sheets. I imported 2 of them from another workbook. No problem till here, but I use formulas to lin some cells into those sheets. The 2 ones I imported were so linked to their original workbook. I found many formulas I change to link them to the new workbook, but it looks like I missed some. I was wondering if it is possible to write a macro that would read the sheet to find formulas and sort them anywhere else like a new sheet, or at least scan the formulas for dependencies or scan for a certain string. I hope to be clear enough :-p

Help would be greatly appreaciated.

Regards,

FrenchCAD
Goodrich Actuation Systems France
Airbus A380 group
cyril.guichard@goodrich.com

RE: How to sort formulas from a sheet?

You can try using Replace (Ctrl-H) and then replace all occurrences of for example [C:\dir\oldsheet.xls] with nothing, so the formula will reference its own workbook.
You can also try Edit - Links... and then Change Source.
There are also some add-ins available that will replace formulas with external references with their values.

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

RE: How to sort formulas from a sheet?

As Joerd suggests and from other discussions in this forum, I found the Microsoft add-in program dellinks.exe to be very useful in finding and eliminating links.

See Thread770-47158

This add-in program can be downloaded free of charge from
http://support.microsoft.com/
search the knowledgebase for dellinks.exe and follow the instructions.

When run, it will keep your original file unchanged and make changes to a copy of the file as well as create a listing of the changes it made.  This includes a listing of the links.

RE: How to sort formulas from a sheet?

(OP)
Many thanks for the help guys

FrenchCAD
Goodrich Actuation Systems France
Airbus A380 group
cyril.guichard@goodrich.com

RE: How to sort formulas from a sheet?

(OP)
Well, I have a problem with Dellinks.exe It seems like the program isn't compatible with my French version of Excel97 (damn compatibility problems  >:[ ). I foolowed the procedure written in the tewt file given, and when I launch Excel, it says "Routine Location Error Code 1 : Please record this Error Code". Then, I add the add-in using Tools>Add-ins and it doesn't appear in my Tools>Wizards menu as it should do. I also got "Routine Location Error Code 2 : Please record this Error Code" when closing Excel.

Dellinks.exe can be downloaded at the following address btw :

http://support.microsoft.com/?kbid=188449

Also, how can I remove add-in listed in the Tools>Add-ins box?

FrenchCAD
Goodrich Actuation Systems France
Airbus A380 group
cyril.guichard@goodrich.com

RE: How to sort formulas from a sheet?

Sorry to hear you had so much problem with dellinks, I found it to be very useful, maybe you can find something similar that would be compatible.

I'm afraid I can't offer much in the way of additional help, besides, I guess I wasn't very helpful to begin with.

I'm running Excel 2000 in the US, but look to see if you have these options.

Go to Tools/Add-ins.  In the Add-ins available window, make sure the box for Delete Links Wizard is not checked.  This should unload dellinks from memory and prevent it from being loaded when you start Excel again.  This, however, does not remove it from your computer or from the Add-ins available window.

I have not removed an Add-in from my computer before but I did a search in Excel help for "remove add-in" and did find instructions on how to do it.  For reference, I've pasted the info I found below, but obviously, you should look to see what your version suggests.  Good luck in resetting your system.

Install or remove individual features of Microsoft Office or Excel
In most cases, when you first attempt to use a feature that is not installed, Microsoft Excel installs the feature automatically. For example, if you click Run Database Query on the Data menu, Microsoft Query is installed. Use this procedure if the feature you want is not installed automatically.

Click Print  on the Help toolbar to print this topic. You might also want to click the link at the end of this procedure and print that topic as well.

Quit all programs.

On the Windows Start menu, point to Settings, and then click Control Panel.

Double-click the Add/Remove Programs  icon, and then do one of the following:
If you installed Excel with Office, click Microsoft Office 2000 on the Install/Uninstall tab, and then click Add/Remove.

If you installed only Excel, click Microsoft Excel 2000 on the Install/Uninstall tab, and then click Add/Remove.

Follow the instructions on the screen.
See the location of features in the installation program.

Notes   

If you installed Office or Excel from a CD-ROM and later mapped your CD-ROM drive to a new drive letter, run the installer again from the CD-ROM. If you are running any Office or Excel files from the CD-ROM, you must uninstall the program and then reinstall Office or Excel from the CD-ROM.

If you originally installed Office or Excel from a network file server or shared folder, run that copy of the installer.

RE: How to sort formulas from a sheet?

The addinns dialogue box doesn't have a delete button, however you can delete the addin using the following procedure:

uncheck the addin, rename the XLA file and then recheck the addin. a meesage will ask if excel should remove from the list, click yes.

best regards

finnigan

RE: How to sort formulas from a sheet?

To supplement Joerd's solution, I would try this

1) Hit "Ctrl-`" key simultaneously with cursor in the worksheet. (NB: the ` key is just next to 1 in the numeral row above the alpha keys)

- this will show up all the formulas in the worksheet

2) Hit Alt, E and E to activate the Replace function, replace the old workbook link with nothing ie leave it blank.

It works for me every time.

RE: How to sort formulas from a sheet?

(OP)
Thanks for the answers. Alas, I have no access to remove Office components, I need to ask my computer service.

Best regards,

Cyril Guichard
Mechanical Engineer

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login


Resources

Low-Volume Rapid Injection Molding With 3D Printed Molds
Learn methods and guidelines for using stereolithography (SLA) 3D printed molds in the injection molding process to lower costs and lead time. Discover how this hybrid manufacturing process enables on-demand mold fabrication to quickly produce small batches of thermoplastic parts. Download Now
Design for Additive Manufacturing (DfAM)
Examine how the principles of DfAM upend many of the long-standing rules around manufacturability - allowing engineers and designers to place a part’s function at the center of their design considerations. Download Now
Taking Control of Engineering Documents
This ebook covers tips for creating and managing workflows, security best practices and protection of intellectual property, Cloud vs. on-premise software solutions, CAD file management, compliance, and more. Download Now

Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close