Home Screenshots Download Buy Now

Search About Contact Us Weblog

You can do this too

Download now, it's easy

Sit down and relax, while ASAP Utilities does the rest!

ASAP Utilities, description of its Excel tools

Language:

Formulas » Insert function from the ASAP Utilities library...

With this utility you can insert a formula from the ASAP Utilities functions library into the active cell.

The ASAP Utilities functions library includes the following functions:

=ASAPCELLCOLORINDEX(cell)

Returns the color index number of the cell.
If you afterwards change the color in the cell, you have to press Control+Alt+F9 to have the formula recalculated.
This function does not recognize formatting if it is applied via conditional formatting.

Parameters:

cell = The cell to get Excel's number for the fill color from.

=ASAPCOUNTBYCELLCOLOR(reference, color_index_nr)

Counts the number of cells in the given range that have a certain fill color.
If you afterwards change the fill color in any of the referenced cells, you have to press Control+Alt+F9 to have the formulas recalculated.
This function does not recognize formatting if it is applied via conditional formatting.

Parameters:

reference = The range of cells to search in.

color_index_nr = The cell that has the fill color to count, or the color index number (1-56) from Excel.

=ASAPCOUNTBYFONTCOLOR(reference, color_index_nr)

Counts the number of cells in the given range that have a certain font color.
If you afterwards change the font color in any of the referenced cells, you have to press Control+Alt+F9 to have the formulas recalculated.
This function does not recognize formatting if it is applied via conditional formatting.

Parameters:

reference = The range of cells to search in.

color_index_nr = The cell that has the font color to count, or the color index number (1-56) from Excel.

=ASAPCOUNTCHAR(within_text, find_text)

Counts the number of times a character occurs in a text
This way you can for example count the number of commas in a cell. This function is case sensitive.

Parameters:

within_text = The text containing the character you want to count.

find_text = The character to count the occurrences of. This has to be a single character.

=ASAPCOUNTSHADES(reference)

Counts the number of colored cells in your range.
If you afterwards change the fill color in any of the referenced cells, you have to press Control+Alt+F9 to have the formulas recalculated.
This function does not recognize formatting if it is applied via conditional formatting.

Parameters:

reference = The range of cells to count the number of cells that have a fill color.

=ASAPEXTRACTFILENAME(text, optional path_separator)

Returns the file name from a full path and filename. By default the formula uses a backslash (\) as separator, but optionally you can specify another separator.
For example =ASAPEXTRACTFILENAME("D:\Projects\Archive\Client 1\Balance.xls") returns "Balance.xls".

Parameters:

text = The value or cell address from which you want to extract only the file name

path_separator = The path separator. Optional, if omitted a backslash (\) is used.

=ASAPEXTRACTFOLDERNAME(text, optional path_separator)

Returns the folder name from a combined filepath and filename. By default the formula uses a backslash (\) as separator, but optionally you can specify another separator.
For example =ASAPEXTRACTFOLDERNAME("D:\Projects\Archive\Client 1\Balance.xls") returns "D:\Projects\Archive\Client 1".

Parameters:

text = The value or cell address from which you want to extract only the folder name

path_separator = The path separator. Optional, if omitted a backslash (\) is used.

=ASAPEXTRACTNUMBERS(text)

Returns the numbers from a text string.
For example the formula =ASAPEXTRACTNUMBERS("8011 LB") returns 8011.

Parameters:

text = The value or cell address from which you want to extract the numbers from.

=ASAPFILENAME()

Returns the name of your workbook. This is the name of the workbook without the filepath (folder).
For example "Balance.xls".

=ASAPFILEPATH()

Returns the filepath (the folder) where your workbook is stored.
For example: "D:\Projects\Archive\Client 1".

=ASAPFONTCOLORINDEX(cell)

Returns the color index number of the font of a cell.
If you afterwards change the font color in the cell, you have to press Control+Alt+F9 to have the formula recalculated.
This function does not recognize formatting if it is applied via conditional formatting.

Parameters:

cell = The cell to get Excel's number for the text color from.

=ASAPFULLFILENAME()

Returns the full filename of your workbook. This is the name of the workbook including the folder (filepath) where it is saved.
For example "D:\Projects\Archive\Client 1\Balance.xls".

=ASAPGETCOMMENT(cell)

Returns the text from the comment a cell.
If you afterwards change the comment in the cell, you have to press Control+Alt+F9 to have the formula recalculated.

Parameters:

cell = The cell to get the text from the comment from.

=ASAPGETDOMAIN(text, optional show_protocol = False)

Returns the (sub)domain from a given hyperlink (website address/url).
For example if cell A1 contains the value "http://www.asap-utilities.com/download-asap-utilities.php" then these are the formula results:
=ASAPGETDOMAIN(A1) returns "www.asap-utilities.com"
=ASAPGETDOMAIN(A1;TRUE) returns "http://www.asap-utilities.com"

Parameters:

text = The value or cell address from which you want to extract the domain.

show_protocol = Optional, logical value, if omitted the default is FALSE. If TRUE this function will also return the protocol of the link, which is the part before the domain such as http://, ftp:// etc..

=ASAPGETFONTNAME(cell)

Returns the name of the font in a cell.
If you afterwards change the font in the cell, you have to press Control+Alt+F9 to have the formula recalculated.
This function does not recognize formatting if it is applied via conditional formatting.

Parameters:

cell = The cell to get the font from.

=ASAPGETFONTSIZE(cell)

Returns the font size of a cell.
If you afterwards change the font size in the cell, you have to press Control+Alt+F9 to have the formula recalculated.
This function does not recognize formatting if it is applied via conditional formatting.

Parameters:

cell = The cell to get the font size from.

=ASAPGETFORMULA(cell)

Returns the formula of a cell.

Parameters:

cell = The cell to get the formula from.

=ASAPGETFORMULAINT(cell)

Returns the formula of a cell in the "international" notation.
The English names for the formulas will be used, the list separator is a comma and the decimal separator is a point.
The largest resources on the Internet on Excel are in English. On these websites the "international" formulas and style are used. If you use a local version of Excel you can now easily create an "international" example of the formula you used.

Parameters:

cell = The cell to get the formula from.

=ASAPGETHYPERLINK(cell, optional text_no_link)

Returns the hyperlink from a cell. The hyperlink can be one of the following types:
- existing file or web page
- place in your document
- e-mail address
If you afterwards change the hyperlink in the cell, you have to press Control+Alt+F9 to have the formula recalculated.

Parameters:

cell = The cell to read the hyperlink from.

text_no_link = Optional, this text will be displayed if the cell doesn't have a hyperlinks. If omitted, the formula will give an empty result for cells without hyperlinks.

=ASAPGETNUMBERFORMAT(cell)

Returns the number format of a cell.
If you afterwards change the number format in the cell, you have to press Control+Alt+F9 to have the formula recalculated.

Parameters:

cell = The cell to get the number format from.

=ASAPISBOLD(cell)

Returns TRUE if the cell is bold or FALSE if it isn't.
If you afterwards change the bold setting in the cell, you have to press Control+Alt+F9 to have the formula recalculated.
This function does not recognize formatting if it is applied via conditional formatting.

=ASAPISFORMULA(cell)

Returns TRUE if the cell has a formula or FALSE if it doesn't.

=ASAPMERGECELLS(reference, optional delimiter = "", optional skip_empty_cells = True)

Joins several text strings into one text string.
An easy alterative for the Excel =CONCATENATE() function. The benefit of this ASAP Utilities function:
- You can specify a range to join, for example "A1:G1".
- The number format of the values will be used. For example if a cell has the value "12.23072" and the number format is to display only one decimal then this function uses value as "12.2".
- You only have to specify a delimiter once.
- By default empty cells will be ignored.

Parameters:

reference = A contiguous range of cells to join the values from. When reading the cell values, their number format will be used.

delimiter = Optional, a character to insert between the cell values. If omitted no delimiter is used.

skip_empty_cells = Optional, is a logical value: to skip empty cells = TRUE or omitted; to include empty cells in the result = FALSE.

=ASAPRANDOMPASSWORD(optional length = 8, optional use_symbols = True)

Returns a random string that can be used as a password.
This function will return a strong password which contains of :
- both uppercase and lowercase letters
- numbers
- special characters, such as ~ ! @ # $ % ^ & * ( ) [ ] \ / < > : - = + _

Parameters:

length = Optional, the length of the password. If omitted the length will be 8 characters. If the length given is less then 8, still a password of 8 characters will be returned.

use_symbols = Optional, is a logical value: to use special characters in the password = TRUE or omitted; to create a password without special characters = FALSE.

=ASAPSHEETNAME()

Returns the name of the worksheet this formula is used on.

=ASAPSPELLNUMBER(ByVal number, optional strLanguage = "EN", optional blnCurrency = False, optional strSingular, optional strPlural, optional strComma, optional strCentSingular, optional strCentPlural)

Returns a spelled-out number or amount.

A few examples if cell A1 contains the value 142.23
=ASAPSPELLNUMBER(A1,"EN", TRUE, "Dollar", "Dollars", , "Cent", "Cents") returns One Hundred Forty Two Dollars and Twenty Three Cents
=ASAPSPELLNUMBER(A1,"EN", FALSE,,,"Comma") returns One Hundred Forty Two Comma Twenty Three
=ASAPSPELLNUMBER(A1,"NL") returns éénhonderdtweeenveertig komma drieentwintig

If a number contains more than two decimals this function will spell out the number as if it was rounded to two decimals.
A practical example where this function can be useful is to write out amounts on cheques.

Parameters:

number = The number or cell with a number you want to spell.

language = Optional, text string representing in which language the number is spelled out: English = EN or omitted, Dutch = NL, German = DE.

currency = Optional, logical value: to spell the number as a currency = TRUE; to spell the number just as a number = FALSE or omitted. For example spell the number 2 as "two dollars and no cents" or just "two".

cur_singular = An optional text string for the currency spelled singular. For example "dollar".

cur_plural = An optional text string for the currency spelled plural. For example "dollars".

comma = An optional text string for the decimal separator used. For example the comma sign (",") or point (".") or the word "comma". If omitted then it will be automatically filled depending on the given language: English = "comma", Dutch and German = "komma".

cur_cent_singular = An optional text string for the word used with currency for the amount behind the comma, singular. For example "cent".

cur_cent_plural = An optional text string for the word used with currency for the amount behind the comma, plural. For example "cents".

=ASAPSTRIPNUMBERS(text)

Removes all numbers from a text string and removes all spaces at the beginning and end of the result.
For example the formula =ASAPSTRIPNUMBERS("8011 LB") returns "LB".

Parameters:

text = The value or cell address from which you want to strip the numbers.

=ASAPSUMBYCELLCOLOR(reference, color_index_nr)

Adds the cells that have a certain fill color.
If you afterwards change the color in any of the referenced cells, you have to press Control+Alt+F9 to have the formulas recalculated.
This function does not recognize formatting if it is applied via conditional formatting.

Parameters:

reference = The range of cells to search in.

color_index_nr = The cell that has the fill color to sum, or the color index number (1-56) from Excel.

=ASAPSUMBYFONTCOLOR(reference, color_index_nr)

Adds the cells that have a certain font color.
If you afterwards change the font color in any of the referenced cells, you have to press Control+Alt+F9 to have the formulas recalculated.
This function does not recognize formatting if it is applied via conditional formatting.

Parameters:

reference = The range of cells to search in.

color_index_nr = The cell that has the font color to sum, or the color index number (1-56) from Excel.

Screenshots

Example screenshot: 1

Example screenshot: 2 Calculate the sum of cells that have a specific color

Example screenshot: 3 Retrieve the comments

Starting this utility

In the Excel menu choose ASAP Utilities » Formulas » Insert function from the ASAP Utilities library...

Additional keywords:
return cell's

Download example workbook that demonstrates these extra functions

We have created a example workbook that shows how to get things done, that are normally not possible in Excel, by using the functions that ASAP Utilities adds.
Example-workbook-ASAP-Utilities-formulas.xls (0.3 MB)

Worksheet functions added by ASAP Utilities

Remarks on calculation

Since Excel only recalculates a formula if the value in a (referenced) cell changes (most of the times), some of the ASAP Utilities worksheet formulas don't get triggered to update. In that case you have to press F9 or Control+Alt+F9 to have the ASAP Utilities formula(s) updated.

Remarks on distributing

If you use these worksheet functions in your worksheet there a few considerations to make if other people are going to work on the same workbook:

Everybody that works with the file using the ASAP Utilities worksheet functions should have ASAP Utilities installed as default add-in.
If somebody is going to work with your file but he/she doesn't have ASAP Utilities, then you should convert the formula-results to their values (Copy » Paste Special » Values) or in ASAP Utilities:
ASAP Utilities » Formulas » Change formulas to their calculated values

If you see the result of a function displayed as "#NAME?" this means that ASAP Utilities is not available. (In case you do have ASAP Utilities installed it can also mean there is a spelling-error in the formula name).

« Return to the list of “Formulas” utilities.

Share this page:

Printer friendly page

Connect with us at:

	Home Screenshots Download Buy Now Search About Contact Us Weblog

	Download License Agreement Download ASAP Utilities (recommended) Upgrade to the latest version Version history Compare versions Alternative download options System requirements Upgrade twice a year (Home and student) Download ASAP Utilities for Excel 97 Purchase Buy Now Payment Methods Pricing and volume discounts Sales, upgrades and Refunds Policy When to buy a license License Agreement Example of the ordering process + help What is it? What is ASAP Utilities Screenshots of ASAP Utilities The primary goals of ASAP Utilities About the author of ASAP Utilities System requirements Nederlands: Makkelijker werken in Excel Examples of how to use ASAP Utilities Why use it? Top reasons to use ASAP Utilities Features Descriptions of all tools in ASAP Utilities Search within the ASAP Utilities tools Added worksheet functions Fully expanded list of all menu items Some screenshots of ASAP Utilities Examples of how to use ASAP Utilities Video tutorials Testimonials Testimonials, feedback from users Reviews from the press Clients, who uses ASAP Utilities? User Guide Download the User Guide How to install ASAP Utilities Detailed step by step installation How to uninstall ASAP Utilities Examples of how to use ASAP Utilities Video tutorials News Latest news RSS Feed Receive our Newsletter Unsubscribe from our Mailing List Excel tips Excel tips Excel keyboard shortcuts overview Headlines from the best Excel weblogs Links to interesting websites Recommended Excel books Excel version screenshots Support 1. FAQ: Questions and answers 2. Search within our website 3. Version history 4. Bug report guidelines 5. Sitemap 6. Contact us
	Home Features FormulasInsert function from the ASAP Utilities library...
	You can do this too Download now, it's easy ASAP Utilities, description of its Excel tools Language: Formulas » Insert function from the ASAP Utilities library... With this utility you can insert a formula from the ASAP Utilities functions library into the active cell. The ASAP Utilities functions library includes the following functions: =ASAPCELLCOLORINDEX(cell) Returns the color index number of the cell. If you afterwards change the color in the cell, you have to press Control+Alt+F9 to have the formula recalculated. This function does not recognize formatting if it is applied via conditional formatting. Parameters: cell = The cell to get Excel's number for the fill color from. =ASAPCOUNTBYCELLCOLOR(reference, color_index_nr) Counts the number of cells in the given range that have a certain fill color. If you afterwards change the fill color in any of the referenced cells, you have to press Control+Alt+F9 to have the formulas recalculated. This function does not recognize formatting if it is applied via conditional formatting. Parameters: reference = The range of cells to search in. color_index_nr = The cell that has the fill color to count, or the color index number (1-56) from Excel. =ASAPCOUNTBYFONTCOLOR(reference, color_index_nr) Counts the number of cells in the given range that have a certain font color. If you afterwards change the font color in any of the referenced cells, you have to press Control+Alt+F9 to have the formulas recalculated. This function does not recognize formatting if it is applied via conditional formatting. Parameters: reference = The range of cells to search in. color_index_nr = The cell that has the font color to count, or the color index number (1-56) from Excel. =ASAPCOUNTCHAR(within_text, find_text) Counts the number of times a character occurs in a text This way you can for example count the number of commas in a cell. This function is case sensitive. Parameters: within_text = The text containing the character you want to count. find_text = The character to count the occurrences of. This has to be a single character. =ASAPCOUNTSHADES(reference) Counts the number of colored cells in your range. If you afterwards change the fill color in any of the referenced cells, you have to press Control+Alt+F9 to have the formulas recalculated. This function does not recognize formatting if it is applied via conditional formatting. Parameters: reference = The range of cells to count the number of cells that have a fill color. =ASAPEXTRACTFILENAME(text, optional path_separator) Returns the file name from a full path and filename. By default the formula uses a backslash (\) as separator, but optionally you can specify another separator. For example =ASAPEXTRACTFILENAME("D:\Projects\Archive\Client 1\Balance.xls") returns "Balance.xls". Parameters: text = The value or cell address from which you want to extract only the file name path_separator = The path separator. Optional, if omitted a backslash (\) is used. =ASAPEXTRACTFOLDERNAME(text, optional path_separator) Returns the folder name from a combined filepath and filename. By default the formula uses a backslash (\) as separator, but optionally you can specify another separator. For example =ASAPEXTRACTFOLDERNAME("D:\Projects\Archive\Client 1\Balance.xls") returns "D:\Projects\Archive\Client 1". Parameters: text = The value or cell address from which you want to extract only the folder name path_separator = The path separator. Optional, if omitted a backslash (\) is used. =ASAPEXTRACTNUMBERS(text) Returns the numbers from a text string. For example the formula =ASAPEXTRACTNUMBERS("8011 LB") returns 8011. Parameters: text = The value or cell address from which you want to extract the numbers from. =ASAPFILENAME() Returns the name of your workbook. This is the name of the workbook without the filepath (folder). For example "Balance.xls". =ASAPFILEPATH() Returns the filepath (the folder) where your workbook is stored. For example: "D:\Projects\Archive\Client 1". =ASAPFONTCOLORINDEX(cell) Returns the color index number of the font of a cell. If you afterwards change the font color in the cell, you have to press Control+Alt+F9 to have the formula recalculated. This function does not recognize formatting if it is applied via conditional formatting. Parameters: cell = The cell to get Excel's number for the text color from. =ASAPFULLFILENAME() Returns the full filename of your workbook. This is the name of the workbook including the folder (filepath) where it is saved. For example "D:\Projects\Archive\Client 1\Balance.xls". =ASAPGETCOMMENT(cell) Returns the text from the comment a cell. If you afterwards change the comment in the cell, you have to press Control+Alt+F9 to have the formula recalculated. Parameters: cell = The cell to get the text from the comment from. =ASAPGETDOMAIN(text, optional show_protocol = False) Returns the (sub)domain from a given hyperlink (website address/url). For example if cell A1 contains the value "http://www.asap-utilities.com/download-asap-utilities.php" then these are the formula results: =ASAPGETDOMAIN(A1) returns "www.asap-utilities.com" =ASAPGETDOMAIN(A1;TRUE) returns "http://www.asap-utilities.com" Parameters: text = The value or cell address from which you want to extract the domain. show_protocol = Optional, logical value, if omitted the default is FALSE. If TRUE this function will also return the protocol of the link, which is the part before the domain such as http://, ftp:// etc.. =ASAPGETFONTNAME(cell) Returns the name of the font in a cell. If you afterwards change the font in the cell, you have to press Control+Alt+F9 to have the formula recalculated. This function does not recognize formatting if it is applied via conditional formatting. Parameters: cell = The cell to get the font from. =ASAPGETFONTSIZE(cell) Returns the font size of a cell. If you afterwards change the font size in the cell, you have to press Control+Alt+F9 to have the formula recalculated. This function does not recognize formatting if it is applied via conditional formatting. Parameters: cell = The cell to get the font size from. =ASAPGETFORMULA(cell) Returns the formula of a cell. Parameters: cell = The cell to get the formula from. =ASAPGETFORMULAINT(cell) Returns the formula of a cell in the "international" notation. The English names for the formulas will be used, the list separator is a comma and the decimal separator is a point. The largest resources on the Internet on Excel are in English. On these websites the "international" formulas and style are used. If you use a local version of Excel you can now easily create an "international" example of the formula you used. Parameters: cell = The cell to get the formula from. =ASAPGETHYPERLINK(cell, optional text_no_link) Returns the hyperlink from a cell. The hyperlink can be one of the following types: - existing file or web page - place in your document - e-mail address If you afterwards change the hyperlink in the cell, you have to press Control+Alt+F9 to have the formula recalculated. Parameters: cell = The cell to read the hyperlink from. text_no_link = Optional, this text will be displayed if the cell doesn't have a hyperlinks. If omitted, the formula will give an empty result for cells without hyperlinks. =ASAPGETNUMBERFORMAT(cell) Returns the number format of a cell. If you afterwards change the number format in the cell, you have to press Control+Alt+F9 to have the formula recalculated. Parameters: cell = The cell to get the number format from. =ASAPISBOLD(cell) Returns TRUE if the cell is bold or FALSE if it isn't. If you afterwards change the bold setting in the cell, you have to press Control+Alt+F9 to have the formula recalculated. This function does not recognize formatting if it is applied via conditional formatting. =ASAPISFORMULA(cell) Returns TRUE if the cell has a formula or FALSE if it doesn't. =ASAPMERGECELLS(reference, optional delimiter = "", optional skip_empty_cells = True) Joins several text strings into one text string. An easy alterative for the Excel =CONCATENATE() function. The benefit of this ASAP Utilities function: - You can specify a range to join, for example "A1:G1". - The number format of the values will be used. For example if a cell has the value "12.23072" and the number format is to display only one decimal then this function uses value as "12.2". - You only have to specify a delimiter once. - By default empty cells will be ignored. Parameters: reference = A contiguous range of cells to join the values from. When reading the cell values, their number format will be used. delimiter = Optional, a character to insert between the cell values. If omitted no delimiter is used. skip_empty_cells = Optional, is a logical value: to skip empty cells = TRUE or omitted; to include empty cells in the result = FALSE. =ASAPRANDOMPASSWORD(optional length = 8, optional use_symbols = True) Returns a random string that can be used as a password. This function will return a strong password which contains of : - both uppercase and lowercase letters - numbers - special characters, such as ~ ! @ # $ % ^ & * ( ) [ ] \ / < > : - = + _ Parameters: length = Optional, the length of the password. If omitted the length will be 8 characters. If the length given is less then 8, still a password of 8 characters will be returned. use_symbols = Optional, is a logical value: to use special characters in the password = TRUE or omitted; to create a password without special characters = FALSE. =ASAPSHEETNAME() Returns the name of the worksheet this formula is used on. =ASAPSPELLNUMBER(ByVal number, optional strLanguage = "EN", optional blnCurrency = False, optional strSingular, optional strPlural, optional strComma, optional strCentSingular, optional strCentPlural) Returns a spelled-out number or amount. A few examples if cell A1 contains the value 142.23 =ASAPSPELLNUMBER(A1,"EN", TRUE, "Dollar", "Dollars", , "Cent", "Cents") returns One Hundred Forty Two Dollars and Twenty Three Cents =ASAPSPELLNUMBER(A1,"EN", FALSE,,,"Comma") returns One Hundred Forty Two Comma Twenty Three =ASAPSPELLNUMBER(A1,"NL") returns éénhonderdtweeenveertig komma drieentwintig If a number contains more than two decimals this function will spell out the number as if it was rounded to two decimals. A practical example where this function can be useful is to write out amounts on cheques. Parameters: number = The number or cell with a number you want to spell. language = Optional, text string representing in which language the number is spelled out: English = EN or omitted, Dutch = NL, German = DE. currency = Optional, logical value: to spell the number as a currency = TRUE; to spell the number just as a number = FALSE or omitted. For example spell the number 2 as "two dollars and no cents" or just "two". cur_singular = An optional text string for the currency spelled singular. For example "dollar". cur_plural = An optional text string for the currency spelled plural. For example "dollars". comma = An optional text string for the decimal separator used. For example the comma sign (",") or point (".") or the word "comma". If omitted then it will be automatically filled depending on the given language: English = "comma", Dutch and German = "komma". cur_cent_singular = An optional text string for the word used with currency for the amount behind the comma, singular. For example "cent". cur_cent_plural = An optional text string for the word used with currency for the amount behind the comma, plural. For example "cents". =ASAPSTRIPNUMBERS(text) Removes all numbers from a text string and removes all spaces at the beginning and end of the result. For example the formula =ASAPSTRIPNUMBERS("8011 LB") returns "LB". Parameters: text = The value or cell address from which you want to strip the numbers. =ASAPSUMBYCELLCOLOR(reference, color_index_nr) Adds the cells that have a certain fill color. If you afterwards change the color in any of the referenced cells, you have to press Control+Alt+F9 to have the formulas recalculated. This function does not recognize formatting if it is applied via conditional formatting. Parameters: reference = The range of cells to search in. color_index_nr = The cell that has the fill color to sum, or the color index number (1-56) from Excel. =ASAPSUMBYFONTCOLOR(reference, color_index_nr) Adds the cells that have a certain font color. If you afterwards change the font color in any of the referenced cells, you have to press Control+Alt+F9 to have the formulas recalculated. This function does not recognize formatting if it is applied via conditional formatting. Parameters: reference = The range of cells to search in. color_index_nr = The cell that has the font color to sum, or the color index number (1-56) from Excel. Screenshots Example screenshot: 1 Example screenshot: 2 Calculate the sum of cells that have a specific color Example screenshot: 3 Retrieve the comments Starting this utility In the Excel menu choose ASAP Utilities » Formulas » Insert function from the ASAP Utilities library... Additional keywords: return cell's back to top Download example workbook that demonstrates these extra functions We have created a example workbook that shows how to get things done, that are normally not possible in Excel, by using the functions that ASAP Utilities adds. Example-workbook-ASAP-Utilities-formulas.xls (0.3 MB) Remarks on calculation Since Excel only recalculates a formula if the value in a (referenced) cell changes (most of the times), some of the ASAP Utilities worksheet formulas don't get triggered to update. In that case you have to press F9 or Control+Alt+F9 to have the ASAP Utilities formula(s) updated. back to top Remarks on distributing If you use these worksheet functions in your worksheet there a few considerations to make if other people are going to work on the same workbook: Everybody that works with the file using the ASAP Utilities worksheet functions should have ASAP Utilities installed as default add-in. If somebody is going to work with your file but he/she doesn't have ASAP Utilities, then you should convert the formula-results to their values (Copy » Paste Special » Values) or in ASAP Utilities: ASAP Utilities » Formulas » Change formulas to their calculated values If you see the result of a function displayed as "#NAME?" this means that ASAP Utilities is not available. (In case you do have ASAP Utilities installed it can also mean there is a spelling-error in the formula name). « Return to the list of “Formulas” utilities. Share this page: More
	Home Sitemap Search List of all utilities Printer friendly page ^ Back to Top © 1999-2012 - Bastien Mensink - ASAP Utilities - Excel tools - A Must in Every Office BV - The Netherlands Connect with us at: