Excel Help & Excel Best Practices Forums

**Dave Hawley**

IGNORE: Kill Autolinked keywords. Filter, sum, zip, lookup, stats, name, search, functions, training, code, password

Lookup With Multiple Conditions, Pull Numbers From Text, Spell Values as Currency......

Current Special! Complete Excel Excel Training Course for Excel 97 - Excel 2003, only ~~$145.00~~. $59.95 Instant Buy/Download

*Download ZIP/RAR*
This add-in is free for personal use. However, if you're feeling generous, or plan to use them in business, please or perhaps make a purchase and get even more freebies! Source code is password protected. If you would like the password, click and download it

After you install the add-in, you will have new Category in Excel's Native "Insert Function" dialog box. Most Custom Functions are grouped together. That is,

B_ =Boolean (TRUE/FALSE) Function

C_ =Color Function

D_ = Date Function,

L_ =Lookup Function.

S_ =String (Text) Function.

PRE200* means the lowest version the custom function will work on. Please note, that the SpellDollarVal is NOT written by Ozgrid. It was written by Microsoft.

Two Column Lookup to Find N'th Occurrence: This UDF will look in the first column in a Table or Range for the N'th occurence of a specified value, then look in a nominated column for another specified value on the same row. It will then return the result from the same row in a specified column.

Sum Every Nth This Custom Function will sum every nth row or cell in the specified range

Sum The X Smallest/Largest Numbers In Row or Column Sums the top/bottom N numbers in 1 column/row range.

Sum by Color-Count by Color: Will sum a range of cells based on their fill color. There is also one which will count cells based on their fill color.

Sum Excel Ranges Diagonally Ever wanted to sum some cells in Excel, but with a twist. Sum cells diagonally top-to-bottom and vice versa, left-to-right and vice versa.

Sum Excel Range Meeting Up To 5 Conditions This Custom Function for pre Excel 2007. If you have Excel 2007, use SUMIFS. It allows you to nominate up to 5 conditions/criteria to be met in corresponding columns.

A Single Function That Will SUM or COUNT Cells By Their Fill Color: Use this one function to specify if the range of colored cells should be summed or counted.

Count Words: Will count words in single cell, or range of cells.

Count or Sum Specified Number In Cell Housing Many Numbers

Extract Numbers from Text Strings: Will extract the numeric portion from a Text String.

List/Return Difference Between 2 Cells Containing Comma Separated Strings

Sort by Color: Will allow you to nominate a range of colored cells to be sorted by the color order chosen. In other words, Sort by color!

Workbook Name in Cell: These two UDF's will place the name of a Workbook into a cell, or the Workbooks File path and name. It also shows the Excel CELL function returning the Workbooks name, file path and active sheet name. Neither of the two examples for the UDF's take any arguments.

VLOOKUP Across Multiple Sheet: This UDF was written by myself to take the place of VLOOKUP when you need to look across ALL the Worksheets in the active Workbook.

Extract Last Word: This one will extract the last word from a string of text.

Non Repeating Random Numbers: A very handy little function which will produce x unique random numbers between any 2 numbers you specify

Get Cell Comment Text Into Cell: Very simple Function that can be used to extract the text from Excel's cell comments.

Interior Cell Color by Index or Name: Very handy function that will return the referenced cells interior fill color as either and index number or it's text name.

Reverse Cell Content: Function that will reverse the content of a cell.

Get Highest Number Between Nominated Range: A Custom Function that will return the highest number in a range, that is between two specified numbers.

Microsofts Convert a Numeric Value into English Words : A very popular function from Microsoft themselves, that spells out numbers.

Microsofts Convert a Currency or Value into English Words : A very popular function from Microsoft themselves, that spells out currency values.

Get Hyperlink Address : Need to extract the underlying address from an email or Web address? This is the one!.

Does Cell Have a Formula : This one is very handy when used with Conditional Formatting as you can map your spreadsheet.

2 Functions To Determine Excel Calculation Status & Mode

Extract File Name from Full Path Name : Function that is used to extract the file name from a full path name.

Does an Excel Workbook Exists or Not?: Function that is used to determine if an Excel Workbook exists or not.

Extract nth Word From Text in Excel: With the aid of Excel VBA we can write a custom formula/function, or user defined function to extract out the nth word from a text string.

How Many Specified Days in a Month: Number of Specified Days in a Specified Month

Determine nth Weekday of a Specified Date : If you need to have formula return different results on the 1st Friday (any weekday) than if it would on the 2nd, 3rd or 4th Friday you can use the Custom Function/Formula here. For example, you may need to determine how many Saturdays have passed so far in a specified month.

Calculate Sliding Scale Tax : Custom Excel Function (Excel Macros) that can be used to calculate tax based on a sliding scale, or bracket tax.

Display AutoFilter Criteria : Custom function that displays AutoFilter criteria being used

Return Date of Last Chosen Day of Given Month : Custom function that displays the date of the last chosen day in any given month.

Return Date of the First, or nth Day of Month : The Custom Function here can be used to return the date of the nth specified day of any given month

Count One Instance of Each Entry : The Custom Function here can be used to count repeated items once.

Lookup any occurrence from any column and offset left or right : Lookup the nth occurrence in any column and return the corresponding cell to the left or right of the match.

The Ultimate Excel Lookup Function This Custom Function will look in any column, unless specified, for the nth occurrence of the specified value and return the corresponding value x rows above or below the found value to the left or right.

*Download ZIP/RAR*
This add-in is free for personal use. However, if you're feeling generous, or plan to use them in business, please or perhaps make a purchase and get even more freebies! Source code is password protected. If you would like the password, click and download it

**Dave Hawley**

Search

A lot of effort goes into making and keeping this forum Search friendly. When searching use ONLY keywords and NEVER assume your answer! 90% of question asked could have been answered by searching.

Thread Titles Are The Most Important Aspect

1) Don't post for YOURSELF only, think about the 1000's per day that search. Number 2 clarifies this.

2) Always use a relevant keyword thread title that titles what you are trying to do! NEVER use thread titles for what you THINK you want. You are posting BECAUSE you don't know.
Read Anatomy Of A Good Thread Title. Opens new window

Asking Your Question

1) NEVER assume we are familiar with your wants and needs. Explain them CLEARLY.

2) Don't get tunnel vision and assume your answer. You ARE posting because you don't know!

ASSUMPTIONS BY QUESTION ASKERS IS THE SINGLE BIGGEST PROBLEM

3) We only want to know what you are trying to do and the structure of you data.

CROSS POSTING

If you MUST post the same question to multiple forums/newsgroups the least you can do is supply a link to the others. Same applies to the other forums/newsgroups, that is, link back to your thread on the Ozgrid forum.

WHY? Nothing annoys those who kindly offer FREE help more than finding out they have wasted their time helping you, only to find out the question has been solved elsewhere, or they are suggesting something already suggested & rejected elsewhere.

**Dave Hawley** · Users Attachments May 2nd, 2007 until June 2nd, 2015

To read these rules in other languages, go to the bottom
Ton READ thesis of rules in OTHER LANGUAGES, go to the bottom
读这些规则在其它语言, 去底部
A leer estas reglas en otras idiomas, vaya al fondo
Pour lire ces règles dans d'autres langues, allez au fond
Leggere queste regole in altre lingue, vada alla parte inferiore
Para ler estas réguas em outras línguas, vá ao fundo
他の言語のこれらの規則を読むためには, 底へ行きなさい
그밖 언어안에 이 규칙을 읽기 위하여는, 바닥에 가십 시요

Before posting your question, try a Search

These rules must be adhered to else your post might be deleted. If you cannot be bothered to take the 5mins, please don't bother posting.

Thread Titles MUST aptly and concisely describe your problem and NEVER what you THINK is your answer! We enforce this with vigor. YOU HAVE BEEN WARNED.
Ozgrid is Not Associated With Microsoft or Google. Ozgrid Retains the Rights to ALL Posts and Threads To Modify as it Sees Fit.
Your question Title must accurately and concisely describe your problem, NOT what you THINK is your answer. Use only relevant keywords. Be considerate of other Excel users who search. Nothing worse than having to click through all search results to see if it matches your problem!

YOU WILL AGAIN BE REMINDED ON THREAD TITLES WHEN STARTING A NEW THREAD. SO THERE IS NEVER AN EXCUSE FOR A NON-COMPLIANT NEW THREAD TITLE.

Please note that this will also help you greatly when you submit your question as you will see a match of Similar Threads after submitting.
READ RULE 1 & 3 AGAIN.
Do not cross-post. This is when you post the same question on other forums, newsgroups etc. This will be wasting the time of the kind volunteers as they could well be trying solve a problem that has been solved elsewhere. This is like ringing 5 different cab companies, jumping in the first that shows and not caring less about the other 4. If you have cross-posted, please at least supply the URL to the other post(s)
Never be rude. Please take the time to thank those who took their time to help you..
It is possible to upload/download files from this forum. OZGRID ACCEPT NO RESPONSIBILITY FOR ANY HARM THEY MAY COME ABOUT FROM THESE UPLOADS OR DOWNLOADS
Never post a question in the Thread of another member. You MUST ALWAYS start you own New Thread.
Ensure Your question is not too vague. Never assume anyone is familiar with your problem. While you can upload/download small attachments you MUST ALWAYS FULLY DESCRIBE YOUR PROBLEM IN THE POST TEXT. If you cannot be bothered to clearly explain your problem, don't expect anyone to bother helping for free. While the person helping may be a true Guru, however, in regards to your problem, he/she can only know what you tell them.
Ensure your question is not too detailed and does not contain irrelevant details. Please be as descriptive, concise and to-the-point as possible. Short, direct & to-the-point questions with apt Thread Titles are ALWAYS answered quickly.
Never post any code without using the Code Tags. Posting of any code without code tags makes your code hard to read and often impossible to be copied for testing. For more information about code tags, Click Here. You can test these out in our Test Area. They are VERY easy to use.

Note:Code Tags are reserved for posting code, please do not use for anything else e.g. formulas
Never Private Message or email your question to other members. ALL correspondence must be kept in your relevant Thread.
Always use the correct Test Area.
It is one question per Thread and that question MUST have a Thread Title in accordance with rule 1. If you find any consequent questions come about (as they often do) and they do not fit under the current Thread Title, start a New Thread with a Thread Title in accordance with rule 1.
No requesting of help to break any passwords Questions asking to break Sheet Protection, Workbook Protection or VBE Protection will be deleted and could get you banned. Supplying code to enable this, will get you banned.
No requesting of help to by-pass security messages Questions asking to by-pass the Security level (macro warnings) of Applications will be deleted and could get you banned. Supplying code to enable this, will get you banned. We have a free downloads page where you can download EnableMacros. zip, a non-harmful way to encourage enabling of Macros. Alternatively, see: How to add a digital signature to a custom macro project in Office 2003 and Office 2002

Remember, for points 15 & 16 above, while your intentions maybe good, there is absolutely no way to control who reads and uses the code to break passwords and/or by-pass security. There are thousands of sites on the www that condone this, WE ARE NOT ONE!
If/when you have a question that is not being answered, locate it and Edit it to fix the problem, then Reply to your own question with the word Bump. This will place the question back to the top. DO NOT simply post the same question again and leave the other one.
To post your question in compliance with ALL the above rules, use the New Thread Button in the most appropriate Question Forum that be seen on the Forum Home, or see below.

The choices are shown in the Question Forums

Thank you for taking the time to read these points and making the forum a better place.

These rules in other languages
German

Spanish

French

Italian

Portuguese

Japenese

Korean

Chinese (Simplified)

**Dave Hawley**

The number 1 mistake made by Excel spreadsheet users is the manner in which they lay out their data. Nobody would consider building their home on poor foundations, yet most Excel users build their data on poor foundations.

Foundations. How to Correctly Lay Out Data

It's not by coincidence that Excel has many more rows than columns.

While Excel is not a true database Application, data within it should be laid out in a database format. By this I mean, you should reserve one Worksheet to enter all related raw data in a classic table format. That is, headings across row 1 of the table and all data laid out in continuous rows directly underneath their appropriate headings.

This will often mean repeating some data row after row, but that is what Excel will expect. Do not leave blanks to represent repeated data.

What's the Advantage?

By having a single Worksheet for all raw data, set-out in the manner described above, you will be able to use all of Excel's built-in features. Such features include Pivot Tables, Sub totals, Lookups, Database Functions, Filters, Outline & Grouping and much much more!

Remember, this data is only raw data, not your final resulting data. Your final resulting data, statistics etc will be relatively easily extracted from your raw data.

See also;
Proper Spreadsheet Design.