Filed under: ASAP Utilities on October 7th, 2005 by Bastien | 5 Comments »

A question I received today from Daniel (Mexico City) :

I will like to suggest you the possibility of including in the formula menu a funtion like "Hide Formula Errors" to stop the anoing NA or VALUE?
Thanks for making this kind's of tools for excel, from now on hopefully I will spend less time in making my complex spreadsheets.

Perhaps I should give this a better place in the ASAP Utilities since it is already there, only I've called it a little different:
ASAP Utilities » Range » Custom formula error message

What it actually does is it builds an =IF(ISERROR()) around it.

Say your formula is =VLOOKUP(B3,F1:G11,2,FALSE), but a corresponding value was not found:

Let's say we want in this case the formula's with an error produce the result zero (0) in stead of the error.

What you can do is build an =IF(ISERROR(old_formula),[value in case of error],old_formula) around it, either manually or with the help of ASAP Utilities.

ASAP Utilities » Range » Custom formula error message

This will replace the original formula with the new error-catching version:
=IF(ISERROR((VLOOKUP(B3,F1:G11,2,FALSE))),0,(VLOOKUP(B3,F1:G11,2,FALSE)))
The main advantage of ASAP Utilities in this case is it's easier and quicker to use because you don't have to type the formula. Plus it can be used in a range of cells with different formula's.

More information about hiding errors on MS Office Online:
http://office.microsoft.com/en-us/assistance/HP030561211033.aspx

Filed under: ASAP Utilities on October 7th, 2005 by Bastien | 5 Comments »

Filed under: ASAP Utilities on October 6th, 2005 by Bastien | 1 Comment »

A question I received today from Daniel (Mexico City) :

I will like to suggest you the possibility of including in the formula menu a funtion like "Hide Formula Errors" to stop the anoing NA or VALUE?
Thanks for making this kind's of tools for excel, from now on hopefully I will spend less time in making my complex spreadsheets.

Perhaps I should give this a better place in the ASAP Utilities since it is already there, only I've called it a little different:
ASAP Utilities » Range » Custom formula error message

What it actually does is it builds an =IF(ISERROR()) around it.

Say your formula is =VLOOKUP(B3,F1:G11,2,FALSE), but a corresponding value was not found:

Let's say we want in this case the formula's with an error produce the result zero (0) in stead of the error.

What you can do is build an =IF(ISERROR(old_formula),[value in case of error],old_formula) around it, either manually or with the help of ASAP Utilities.

ASAP Utilities » Range » Custom formula error message

This will replace the original formula with the new error-catching version:
=IF(ISERROR((VLOOKUP(B3,F1:G11,2,FALSE))),0,(VLOOKUP(B3,F1:G11,2,FALSE)))
The main advantage of ASAP Utilities in this case is it's easier and quicker to use because you don't have to type the formula. Plus it can be used in a range of cells with different formula's.

More information about hiding errors on MS Office Online:
http://office.microsoft.com/en-us/assistance/HP030561211033.aspx

Filed under: ASAP Utilities on October 6th, 2005 by Bastien | 1 Comment »

Filed under: ASAP Utilities, General on October 4th, 2005 by Bastien | 4 Comments »

(Update October 18, 2007: If you want an easier way to do this, see this post: Video: How to sort by color in Excel with ASAP Utilities)

In Excel you can only sort by value, not by formatting such as cell colors. With ASAP Utilities you can! Here's a short example how to do sort by color. For this I've built a custom worksheet function into ASAP Utilities (added October 2004) that extracts the color of a cell: =asapcellcolorindex(range)

In order to sort by cell color we need to create an extra column that will list all colors as numbers (cell color index) which we can then use to sort by.
I've created an example workbook to show it.

This is what your data might look like at the start (excuse me for the girly colors):

Enter the formula: =asapcellcolorindex(B2) to sort by cell color. In case you want to sort by font color use the function =asapfontcolorindex(B2)

Copy the formula down to the last cell (shortcuts: Controls+C » Control+Alt+L » Control+V)

In the menu choose Data » Sort:

The result:

Important
This formula does not recalculate itself if you change a color. Only if you change a cell's value and a normal calculation is performed, so if you only change a color, you need to press F9 or Control+Alt+F9 te recalculate the cell-colors.

If you want to know more about this technique I recommend an article Chip Pearson wrote: www.cpearson.com/excel/SortByColor.htm

You don't have ASAP Utilities?
Download it at www.asap-utilities.com/p_dl.php (free).

Filed under: ASAP Utilities, General on October 4th, 2005 by Bastien | 4 Comments »

Filed under: ASAP Utilities on October 4th, 2005 by Bastien | Comments Off

Yestday Graham (UK) asked me the following question:

Dear Bastien,

Many thanks for a good program.

To solve this I emailed him to do the the following which work perfectly if you want to change the location of you ASAP Utilities installation:

1. When you now start Excel you get the error. Just click okay, and in the menu goto Tools » Add-ins.
2. Uncheck the box thex to ASAP Utilities. Excel askes you to remove ASAP from the list, answer yes.
3. Now close Excel and restart, we're going the add ASAP again, from the new location:
4. On the tools menu, choose Add-Ins.
5. Use the "Browse..." button to locate the ASAP Utilities.xla file (in the new folder)
6. If Excel askes you to copy the file to the library folder, answer "no".
7. Check the "ASAP Utilities" checkbox.
8. ASAP Utilities is now available again. Have fun!

Filed under: ASAP Utilities on October 4th, 2005 by Bastien | Comments Off

Filed under: Excel 2007 info on October 2nd, 2005 by Bastien | Comments Off

Excel and some other Office 12 applications will have a "save as PDF" function. This was posted yesterday by Dick Kusleika (Daily Dose of Excel) and Chris Pratley (Chris_Pratley's OneNote WebLog) who both joined the 2005 MVP Summit.

A quote from Cris' website:
Today was a fun day. It was our last day with our MVPs visiting here at Redmond. This was one of the best MVP Summits for me - there was a lot of good positive interchange and solid feedback given by the MVPs on all our products. We demoed Office 12 applications in detail so people got a real sense of the totality of Office12. Lots of smiles!

One big smile was when Steven Sinofsky announced during his wrap-up Q&A this morning that Office 12 apps (OneNote included) will support "Save as PDF" natively. He then went through a demo of most of the apps showing each of them saving to PDF. Publisher (another app whose design team I manage) will even support CMYK output for professional pre-press work. Actually, the Publisher team did most of the work to support PDF over the last year. Then the other teams hooked up to the core PDF generator that they wrote from scratch.

It would be great if Excel 12 can also import PDF files.

Filed under: Excel 2007 info on October 2nd, 2005 by Bastien | Comments Off

Filed under: ASAP Utilities on September 29th, 2005 by Bastien | 6 Comments »

Just got a question from Scott:

I absolutely love this program. My main reason for using this program is finding duplicate serial numbers in a list of 12,000 without having insert a conditional formula every time.
I see you can select, hide, or delete all the duplicates but is there anyway to delete the duplicates and leave one of them?

Yes there is:
ASAP Utilities » Range » Empty duplicates in selection.
This will remove the duplicates leaving one original.

If you want to delete entire rows you can specify to leave one original:

Filed under: ASAP Utilities on September 29th, 2005 by Bastien | 6 Comments »

Filed under: Excel 2007 info on September 28th, 2005 by Bastien | Comments Off

Last monday David Gainer from Microsoft has posted more details on Excel 12 on his weblog. Much of the new expanded limits have to do with the new grid.
http://blogs.msdn.com/excel/archive/2005/09/26/474258.aspx

Personally I really like the new grid. More rows and more columns allow you to import more data for analysis.

Filed under: Excel 2007 info on September 28th, 2005 by Bastien | Comments Off

Filed under: ASAP Utilities on September 28th, 2005 by Bastien | Comments Off

Yesterday I got an email from Peter from New Zealand: "Great utility - I got it from the Roam magazine CD. Thanks". This made me curious cause I didn' t know about the article (although the publisher should have contact me first). So I asked Peter to send me scanned copy of the article, which I received today (thank you Peter!).

I realy love it when people write about ASAP Utilities. I like to help people and the more ASAP Utilities gets publicity the more people can profit from the benefits.

The article reads:
Roam proudly presents the essential add-in for all Excel users. ASAP Utilities contains over 300 useful and powerful utilities to automate frequently-used tasks and fill the gaps that Excel's programmers left out. From entering and formatting data to juggling formulae, printing and managing sheets, viewing workbooks, importing and exporting data and more, the ASAP Utilities will save you hours of time and sends nomadic number-crunchers of the mobile world into paroxsyms of joy! ASAP Utilities works with Excel 97, 2000, 2002/XP and 2003.

About Roam Magazine
Roam magazine gives you a dedicated mobility serving of expert product reviews and industry analysis, must-read features and special reports plus practical hands-on techniques for getting the most from your mobile kit and life on the road.
Roam is Australia's first and only "100% mobile computing" magazine!

Readership Profile
While other PC magazines pay mere lip service to mobile computing, or ignore this booming market altogether, Roam is Australia's first and only "100% mobile computing" magazine.

The target audience scopes from solo to SME IT professionals, business decision makers to business travellers, and 'prosumers' -- tech-savvy consumers who drive and influence the mainstream market, and to whom friends and family members turn to for advice on what to buy.

The primary demographic is aged 25-49, AB male and high income earners.
http://www.derwenthoward.com.au/magazine.asp?mag=23

Filed under: ASAP Utilities on September 28th, 2005 by Bastien | Comments Off

Filed under: ASAP Utilities on September 27th, 2005 by Bastien | Comments Off

Yesterday I discovered ASAP Utilities is mentioned in the Russion edition of PC Magazine on 16 сентября, 2005 (16 september?):

ASAP Utilities 3.08 (бесплаÑ‚но) — превосходный набор из 300 элеменÑ‚ов меню, от отмены выбора ячеек до преобразования текст-дата и экспорта выбранной информации в формате HTML. Многие функции свертывают многоэтапные задачи, например открывание папки текущего файла в Explorer, в команды, выполняемые одним щелчком мыши. С помощью других выполняются сложные вычисления. Этот модуль расширения необходим всем пользователям. (eGate Internet Solutions, www.asap-utilities.com.

I don't read Russion but I believe it's the same article that has been published by PC Magazine in its 2005 Ultimate Utility Guide.

Filed under: ASAP Utilities on September 27th, 2005 by Bastien | Comments Off

Filed under: Excel 2007 info on September 23rd, 2005 by Bastien | Comments Off

Today David Gainer, Group Program Manager for Microsoft Excel has started an official Excel blog. Until the public release of Excel 12 (medio 2006 probably) he will publish about new stuff in Excel 12.

Well the first news he posts is great, more rows and columns! (like Quattro Pro has for years).

From his site:
Probably the most common question the Excel team gets from our customers is 'when are you going to add more rows and more columns'. There are many different scenarios behind these requests. Some customers want to be able to analyze more data than Excel has rows, some customers want to track more daily information than Excel has columns, and other customers want to perform matrix math on large matrices of thousands of elements. There are plenty of other scenarios too. Well, the answer to the question is 'in Excel 12' Specifically, the Excel 12 grid will be 1,048,576 rows by 16,384 columns. That's 1,500% more rows and 6,300% more columns than in Excel 2003, and for those of you that are curious, columns now end at XFD instead of IV.

This is an exciting feature for us, because it is a feature that helps a very broad range of our customers, and we are looking forward to seeing what folks create with a bigger grid.

http://blogs.msdn.com/excel/archive/2005/09/23/473185.aspx

After mostly graphical changes (UI) we heard the last weeks which should make the already existing functionality easier te find, todays news shoud take some skepticism away.

Filed under: Excel 2007 info on September 23rd, 2005 by Bastien | Comments Off