J-Walk & Associates, Inc.

Favorite PUP Features?

I'm always curious to know which features in PUP are used most often.

In the next version, I think I'll add some type of automated counter that increments every time a PUP utility is accessed. Then I could invite users to share their counters.

The PUP features that I use most frequently are:

• PUP Bookmarks -- it's probably the most useful thing I've ever written.
• Text Tools -- I tend to import a lot of data, and this utility is really helpful in cleaning up the data

• View Active Workbook's Folder -- When I'm working on a file, I often need to access other files in the same directory. This utility is the fastest way to get to those files.
• Save Sheet As HTML -- It's nice to be able to create an unbloated HTML table.
• Chart Data Labeler -- Every chart user needs to do this at one time or another. Amazingly, Excel still doesn't allow you to specify an arbitrary range for data labels.
• Workbook Link Finder -- Really useful when someone sends me a workbook with lots of links.
• Worksheet Map -- Useful for getting an idea of how an unfamiliar worksheet is laid out.

What are your favorites?

- Reader Comments -

Following are comments in response to this item.
The most recent comment is at the bottom.

1. By Tianwei. Comment posted 20 August, 2008 1:16pm

   1) Text tools - very useful set of tools... use it all the time I aggregate a lot different inputs - one wish list: an option to delete a user specified charater in addition to the normal charater/numeric/non print
   
   2) Formula tools - exact copy and absolute/relevant conversion used most often
   
   3) Select by value/format - extremely useful in cleaning up raw data outputs - well done!
   
   4) Math without formula and alternate row shading very handy too. Error condition used to be before Excel 2007
   
   I havent used a whole lot of charting and report tools... I still like them a lot just not heavy in my day to day.
   
   Biggest wish list: I really want a mass "unlink" command to convert all the links to values as is now. Often time when I receive 20 different spreadsheets back people build-in numerous links to their other files. If I accidently hit "yes" to update links I will have to spend 15 mins to cancel bad link dialog boxes. J-walk, can you please help on this one?!
2. By John Walkenbach. Comment posted 20 August, 2008 3:16pm

   Tianwei, the Edit Links dialog box has a "Break Link" button that will convert links to their current value.
3. By sam. Comment posted 20 August, 2008 9:21pm

   John,
   
   1.Range Randomize
   2.Combine Colums or Rows
   3.Transform Vertical Range
   4.Error Condition Wizard
   5.Perpetual Calendar
   6.Date Picker Toolbar
   
   A few suggestions
   Error Condition Wizard -
   Ex Index(Rng,Match(WhatToMatch,WhereToMatch,WhatKindofMatch))
   The Wizard should trap the Function that generates the Error, rather than the entire formula
   
   If(Iserror(Match(WhatToMatch,WhereToMatch,WhatKindofMatch)),"Not Found",Index(Rng,Match(WhatToMatch,WhereToMatch,WhatKindofMatch)))
   
   I do not know what logic could be used to find the function that can generate the error ?
   
   Perpetual Calendar
   Allow for a option to insert a calendar in the Active sheet
   
   Sam
4. By Tianwei. Comment posted 21 August, 2008 6:02am

   Thanks John. I actually didnt notice the "break link" now standard in Excel 2007. Our company still use Excel 2000 as standard version. I have 2007 running concurrently. Break link was in powerpoint 2000 but somehow not available in Excel then. Anyway, good to know my biggest headache is going away. Thanks!
5. By Scooby Doo. Comment posted 28 August, 2008 9:17am

   I use that Chart Data Labeler a lot. That's a great utility. I also like your Unit Conversion utility, although I added a few conversions e.g. million gallons per day to cubic feet per minute. I like your vinterpolate and hinterpolate -- I was inspired by those to write a combo of these that looks up values in a table interpolating both horizontally and vertically, it would be neat to add. I agree with adding the error trapping function, I made one that is called IfError. It goes, IfError(<expression>, value to return if expression results in error). It would be a useful one to add to your tools (actually -- maybe 2007 already has this functionality, so maybe just update your PUP 6 to help out us poor slobs who haven't upgraded yet).
6. By Ian Huitson. Comment posted 08 September, 2008 5:18pm

   Its Worksheet Map, Text Tools and Resize Chart for me
   But I use lots of the other functions regularly
   
   John, Why not build in an automatic report back to you function, everybody else does it these days !
   
   Hui...
7. By Scott. Comment posted 09 September, 2008 12:54pm

   I use the text tools a good amount, but mainly for adding or removing text from a range rather than conversions (like UPPER or lower).
   
   Frankly, the most unusual and useful feature I use is the bookmark toolbar. We've got so many spreadsheets going on here, it's a great way to tag the ones you want to keep track of or that you use most often.