XLOOKUP Function
Samples provided on each tab showing different ways of using the new XLOOKUP function. This function became available during the summer of 2019 and provides a whole lot more versatility than the VLOOKUP.

Active Cell Coloring
Macro changes the active cell color to yellow and when left it reverts back to its original color.

Discount Function
User defined discount functions applied as a regular built-in spreadsheet function to calculate the discounted amount.

TVM - Time Value of Money Calcs
Some basic financial calculations are provided on this example.

INDEX MATCH Example
INDEX MATCH is a lot more powerful function than VLOOKUP.

Excel Tips and Tricks
Many practical and useful Excel tips and tricks can be found on this document.

Absolute to Relative Value Conversion - From OzGrid
This macro was created by OzGrid for business applications and could be highly useful on a huge page of data that needs to be converted between absolute to relative addresses depending on the types of operations that need to be applied on the same range of data.

Array Formula Example
Array functions are entered as [Ctrl+Shift+Enter] key combination into a cell not only by hitting the Enter key alone. Their power is looking into a range defined in the formula for the given criteria to perform the desired operation.

Display Respective Values
VBA on this file looks at the values in the range and show their corresponding values on the target or destination tab.

Indirect Function
Change the value in the yellow cell and see how the column values respond accordingly with the use of INDIRECT function.

Extract and Display File Names
Macro on this file gets and displays he file names in the selected drive and folder.

Dynamic Range Pivot Table Update
Macro updates the pivot table range for dynamically changing ranges.

Totals by Cell Color
VBA to calculate totals by cell color.

Refresh Multiple Pivots
VBA code on this file refreshes multiple pivot tales with dynamically changing data ranges.

Compare Two Ranges for Changes
VBA in this file compares two ranges and if they are not identical creates another file indicating the cell in red where change occurred.

Email File Sender
VBA on this file emails the named files on the list from their original location to their corresponding email addresses on the list.

Create Access Table from within Excel
Click on the button on the tab and it will create an Access file and append the data on the tab to a table in this Access file.

Extract Year from Text
On this file GetYear function which is a user defined function acting like another built-in Excel function extracts the year from a text in a selected cell where ever that year is mentioned within the text. Using string functions in this case don't help unless you know where that year is located in the cell.

Combo Box Auto Feed for Selections
VBA code in this file feeds the associated items form the raw data set per the selection in the first column. On the properties of the Combo Box object, the linked range properties is left blank and it is populated dynamically after the selection is made in the first column. To make sure that code works after opening up the file make a selection in the first column in any of those boxes then check the items that get displayed on the associated drop down box positioned next to that selection. On file open when you get a prompt click yes to continue.

Delete Rows Per Citeria
VBA in this file deletes all the rows designated as Delete. This is done without manually filtering for the criteria and carefully deleting only those rows from the range and gives flexibility if this operation is to be done repeatedly after each load of new data set. 2nd tab is the original data set. On file open when you get a prompt click yes on it.

To create a personal ribbon with only the desired menu items available.

Excel XLOOKUP Example
This file has various working examples to illustrate how to use XLOOKUP function instead of VLOOKUP which offers a whole lot more power and versatility for spreadsheet based analysis.