# Microsoft Excel - From Beginner to Expert in 6 Hours/ EXCEL DASHBOARD REPORTS

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

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 AND CODE CAN BE FOUND HERE. Want even more FREEBIES?

1. 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.

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

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

4. 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.

5. 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.

6. 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.

7. 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.

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

9. Count or Sum Specified Number In Cell Housing Many Numbers

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

11. List/Return Difference Between 2 Cells Containing Comma Separated Strings

12. 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!

13. 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.

14. 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.

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

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

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

18. 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.

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

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

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

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

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

25. 2 Functions To Determine Excel Calculation Status & Mode\

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

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

28. 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.

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

30. 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.

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

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

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

34. 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

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

36. 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.

37. 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.

Single user license =2 users 2 computers| Unlimited user license = unlimited users unlimited computers | Resellers user license = Do anything you like with the add-in, including rebranding and reselling. That is, NO RESTRICTIONS AT ALL. Please email the support address and include your order number so we can send you the code password. Resellers user license ONLY

Excel Dashboard Reports & Excel Dashboard Charts 50% Off

Become an ExcelUser Affiliate & Earn Money

Special! Free Choice of Complete Excel Training Course OR Excel Add-ins Collection on all purchases totaling over \$64.00. ALL purchases totaling over \$150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to [email protected] 31 days after purchase date.