The ISREF function returns TRUE for a valid worksheet reference and FALSE is not.
In this case, we want to find out of a particular sheet exists in a workbook, so we construct a full reference by concatenating the sheet names in column B with an exclamation mark and "A1":
...Read more
The core of this formula is the LEFT function which simply extracts text from the file name, starting at the left, and ending at the character before the first period (".").
=LEFT(filename,characters)
The FIND function is used to figure out...Read more
The core of this formula is the DATE function, which is used to assemble a proper Excel date value. The DATE function requires valid year, month, and day values, so these are parsed out of the original text string as follows:
1. The year value is extracted with the RIGHT function:...Read more
Working from the inside out, we use the DATEDIF function to calculate how many complete years are between the original anniversary date and the "as of" date, where the as of date is any date after the anniversary date:
DATEDIF(B5,C5,"y")
...Read moreIn Excel, dates are simply serial numbers. In the standard date system for windows, based on the year 1900, where January 1, 1900 is the number 1. This means that January 1, 2050 is the serial number 54,789.
In this example, the goal is to calculate the time remaining before an expiration date. There are many ways to do something like this in Excel, and in this article, we'll look at three different approaches:
Working from the inside out, we use a standard INDEX and MATCH function to locate the first match of lookup values in column B:
INDEX(data,MATCH(B5,data,0))
The MATCH function gets the position of the value in B5 inside the named range data...Read more
Conditional formatting is evaluated relative to every cell it is applied to, starting with the active cell in the selection, cell B3 in this case.
To highlight matching rows, we use this logical expression:
$B4=$K$5
The reference...Read more
Inside the AND function there are two logical criteria. The first is straightforward, and ensures that only cells that match the color in E5 are highlighted:
$B3=$E$5
The second test is more complex:...Read more
In this example, the goal is to get all names in a given group into separate rows grouped by column, as seen in the worksheet above. This is sometimes referred to as a "pivot" operation. The idea is to restructure the data into multiple columns where each column holds the names that belong...Read more