0
penniless

Any Excel wizards out there?

Recommended Posts

Quote

Very cool. Thank-you.



You can even set a format based on the contents of other cells, by using a formula as the format criterion. So in my grade book I can have all the "F" students' names come up in red.
...

The only sure way to survive a canopy collision is not to have one.

Share this post


Link to post
Share on other sites
Quote

Quote

Very cool. Thank-you.



You can even set a format based on the contents of other cells, by using a formula as the format criterion. So in my grade book I can have all the "F" students' names come up in red.



I never realized Excel was this flexible. Prof, did you take classes for this, or was it hit-n-miss learning? I'm just starting to discover the power of SPSS, and it has peaked my curiousity in Excel in the process. (The formats are quite similar.)
It's your life, live it!
Karma
RB#684 "Corcho", ASK#60, Muff#3520, NCB#398, NHDZ#4, C-33989, DG#1

Share this post


Link to post
Share on other sites
Its actually easier to do this by going into the VBA editor and setting workbook properties with some short code.

If you want to build up on Excel knowledge perhaps you may like to check out the Excel-G List server. Plenty of people on there willing to help from anything from simple basics to more complex VBA.

http://peach.ease.lsoft.com/archives/excel-g.html

Share this post


Link to post
Share on other sites
Excel is amazing. It is capable of so much more than most people know. My last company sent me to excel training classes. I learned a lot. Check into it, or hire me as your private tutor.

___________________________________________
meow

I get a Mike hug! I get a Mike hug!

Share this post


Link to post
Share on other sites
Quote

Its actually easier to do this by going into the VBA editor and setting workbook properties with some short code.



http://peach.ease.lsoft.com/archives/excel-g.html



How can it be easier than clicking on a menu and entering the formula?

Real men program in binary and enter the code using toggle switches on the front panel. (Yes, I'm old enough to have done this).
...

The only sure way to survive a canopy collision is not to have one.

Share this post


Link to post
Share on other sites
Another Excel question...

I have a spreadsheet that has about 80 different worksheets. I use it to prepare monthly valuations of a limited partnership. It references other spreadhseets and gets values from them.

A while back, I changed from OS 9 to OS X and when I copied my data to the new machine and new OS the file path had changed due to OS X's different way of organizing things.

I went through the spreadsheet and searched out every reference to the old path and updated it to the new one....but I still get the message "The workbook you opened contains automatic links to information in another workbook" When I click on the button to update the workbook with the information from the other workbook I get 4 screens where it is looking for 4 other workbooks...I can't find these work book references anywhere.

ANy idea on how to track down the references to these other workbooks other than selecting each worksheet and searching it individually?
--
Murray

"No tyranny is so irksome as petty tyranny: the officious demands of policemen, government clerks, and electromechanical gadgets." - Edward Abbey

Share this post


Link to post
Share on other sites
Rev,

The problem would be the same if I had to change the path on a pc and couldn't find a path reference in the spreadsheet.
--
Murray

"No tyranny is so irksome as petty tyranny: the officious demands of policemen, government clerks, and electromechanical gadgets." - Edward Abbey

Share this post


Link to post
Share on other sites
>>ANy idea on how to track down the references to these other workbooks other than selecting each worksheet and searching it individually? <<

Nope. Use Edit Find to look for come text that only the bad path contains.

If the Edit Links dialogue is smart enough, you can select the links to the bad workbooks and then break them there. The problem is that if there really is a reference to that old path somewhere, now you have a hard-keyed number in your supposedly "live" spreadsheet.

----------------------------------
www.jumpelvis.com

Share this post


Link to post
Share on other sites
Quote

Rev,

The problem would be the same if I had to change the path on a pc and couldn't find a path reference in the spreadsheet.



I know, I know, but nobody ever points anything out on a Mac. Should this have happened on a PC, there would have been a dozen or more posts saying "Get a Mac!"

Smile, it's all good! :)
It's your life, live it!
Karma
RB#684 "Corcho", ASK#60, Muff#3520, NCB#398, NHDZ#4, C-33989, DG#1

Share this post


Link to post
Share on other sites
Quote

>>ANy idea on how to track down the references to these other workbooks other than selecting each worksheet and searching it individually? <<

Nope. Use Edit Find to look for come text that only the bad path contains.

If the Edit Links dialogue is smart enough, you can select the links to the bad workbooks and then break them there. The problem is that if there really is a reference to that old path somewhere, now you have a hard-keyed number in your supposedly "live" spreadsheet.



I was afraid that would be the answer..:( I have searched every worksheet as you describe and haven't found the links to the files it searches for when I open the file. I guess I'll just live with it or maybe I'll start a new spreadsheet as of January 1/05 once I have all the year end numbers finalized.
--
Murray

"No tyranny is so irksome as petty tyranny: the officious demands of policemen, government clerks, and electromechanical gadgets." - Edward Abbey

Share this post


Link to post
Share on other sites
I did some searching on "Edit Links" and found a reference to a tool available from Microsoft's website called (ta-da) "Delete Links Wizard".

I just finished using it...and it worked like a charm!!! The links it was looking for were all print areas on various worksheets...45 in all....and not links that showed up when searching the worksheets.

I also found a useful tip for searching multiple worksheets...right click on the tab of a worksheet and then choose.."Select all" and you select all worksheets at the same time and your search will extend to all worksheets.
--
Murray

"No tyranny is so irksome as petty tyranny: the officious demands of policemen, government clerks, and electromechanical gadgets." - Edward Abbey

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

0