The other day I was reading a post over at the Contextures blog about Dynamic Dependent Excel Drop Downs and realized that using an Excel Table would provide an alternative method that is both simple and flexible. Tables are available in Excel versions 2007, 2010, and 2011.
In this post I’ll create a Table to hold the Category’s and Items, create three defined names using dynamic formulas, then use Data Validation to create two drop-down lists, the second being dependent upon the first.
Create at Reference Table
Here’s a Table with Category names in the header row and Items… Read the rest
A few years ago I found myself just south of Paris, France one Sunday with a car and the inclination to do some sight-seeing. Since I was alone and don’t speak French my saving grace was Google Earth and a Tom Tom GPS navigation device.
Tom Tom allows GPS coordinates to be entered as a destination, but Google Earth lists those coordinates in a different format. In researching this article I found out that GPS coordinates can be presented in at least four different formats, making it difficult to understand the coordinates.
Google Maps view of the Eiffel
… Read the rest
I use the Woopra desktop app to view live visitors to this WordPress blog. Visitors will see a Woopra window lurking about when I’m logged into my (free) Woopra account. One option is to initiate a chat session with me.
My first chat happened the other day. The chat notice startled me, but I quickly recovered and was able to interact with a visitor.
(Bold and color are my additions for clarity)
08:54:22. I WANT TO CONVERT 01:05 = 65 MIN WHAT I DO
08:55:42. IN EXCEL
08:56:58. In cell … Read the rest
The INDEX and MATCH functions are used together to perform lookups. The INDEX functions grabs a cell from an array, and the MATCH function contributes the row and/or column position.
INDEX and MATCH Example
Familiarity with the VLOOKUP function can cause you to restrict INDEX and MATCH to less than they can be. The example spreadsheet below bears this out.
The formula for Fiscal QTR takes the Month in cell E2 as input, finds that value in the Month column, then returns the Fiscal QTR in the same row. The first formula is:
This INDEX formula uses the array … Read the rest
The INDEX and MATCH functions working together are more flexible than using the VLOOKUP function. However, if you’ve never used INDEX and MATCH to perform a lookup it can get confusing.
I’m going to cover the INDEX function here so you can learn it well and don’t have to think about it too much when we put it together with the MATCH function.
The INDEX Function Details
The INDEX function returns a cell value from a range, given a row and/or column position number. The syntax is:
INDEX(array, row_num, [column_num])
- Array is required and is a range of cells or
… Read the rest
The MATCH and INDEX functions working together are more flexible than using the VLOOKUP function. However, if you’ve never used MATCH and INDEX to perform a lookup it can be confusing at first.
I’m going to cover the MATCH function (Excel) here so you can learn it well and don’t have to think about it too much when we put it together with the INDEX function. First comes the dirty details and boring syntax, followed by a couple of examples.
The MATCH Function (Excel) Details
The MATCH function searches for an item in a range of cells and returns its … Read the rest
Formulas and Functions give you answers based upon input. If you know what a formula’s answer should be, but can’t derive the input value, Goal Seek is the tool for you.
I have a simple formula from my last post that takes one input, a Date in “m/d/yyyy” format, and returns the Day of the Year. This will tell me that Valentine’s Day — 14 Feb — is the 45th day of the year, or my birthday is day 311 this year.
The quickest way to find out what Date the 250th day of the year falls on … Read the rest
Given the Date in this spreadsheet, a simple formula using the DATE Function will return the Day of Year. Go ahead and type a Date value in cell A2 to see how the formula works, the spreadsheet is embedded from my SkyDrive. (Date format is US, “m/d/yyyy”)
Click here if you can’t see the embedded worksheet.
The Day of Year Formula
The formula in cell B2 is:
The DATE Function takes the Year from the Date you type in cell A2, and uses the zero (0) day of month 1 (January), which is the last day … Read the rest
The DATEDIF Function comes in handy when you want to know how many years, months, and days there are between two dates. And even though it may be slightly flawed, it still beats the alternatives.
The following spreadsheet works well for birthdays or anniversaries. The DATEDIF Function is being used to generate the Years, Months, and Days.
The End Date column has the formula =TODAY().
The Years heading in cell D2 has the custom format ;;;”Years” which shows the text “Years” in the spreadsheet cell while hiding the underlying value “y” that you can see in the formula bar.… Read the rest
Excel’s TRIM Function is a great way to remove extra spaces before, after, and within text in a spreadsheet. However, if you think the identically named VBA function does the same thing you’d be sadly mistaken, as I was once upon a time.
Here’s a normal sentence.
Here’s a sentence with extra spaces – two before, one after, and several within the sentence.
Here’s a comparison of the two TRIM Functions.
The Excel TRIM function reemoves all spaces from text except for single spaces between words.
The VBA TRIM Function only removes leading and trailing spaces.
The VBA TRIM Function
… Read the rest
VLOOKUP is an Excel Function that’s used to look up something. It takes four arguments. The first argument is the Something. The second and third arguments are the Look Up part. And the last argument is optional, but the most important, and is either TRUE or FALSE.
Technically the first argument is the lookup_value, but it’s just the Something we’re going to use in the Look Up part. The second and third arguments are the table that holds the look up data and column where the return value resides.
The VLOOKUP Function takes the Something and tries … Read the rest
Every once in a while you get fixated on something and the only way to let it go is to share it with others. So …. while reviewing the logical IF function and nested IF statements, I came up with a chart or table, that sort of evolved out of curiosity.
It started by looking at two conditions, both having true or false possibilities, as shown in the table below, where A and B have four possible outcomes.
If you type the lowercase true or false into a cell they will change to uppercase TRUE or FALSE. That’s because … Read the rest
I don’t like it when Excel worksheet functions are different from their VBA counterparts. Makes for some aggravation.
For example, I have a user generated data range that’s supposed to be made up of either numbers or empty cells. However, Excel users sometimes bump their keyboards inadvertently (when you hit the space bar) and things like a space character can get entered into an otherwise empty cell, and go undetected to the naked eye.
As part of a VBA macro I looped through each cell in this data range and performed an operation for any cell value … Read the rest
Recently I was asked how to subtract time in Excel (time difference) or how to calculate the number of hours between two points in time on different days. Since this was in a reader comment, I gave a brief answer that requires a fuller account here.
Dates and Times are all part of the master plan in Excel. Once you “get” the fundamentals, the rest is just icing on the cake.
A Date value in Excel looks like this: 40519
A Time value in Excel looks like this: 0.58333
Cell formatting changes how you see these numbers.
The Date: 7 … Read the rest
I’ve a simple formula for calculating the Xth Weekday of ANY month. It takes four inputs: Year, Month, Week, and Day. And requires a couple of lookup tables for data validation, one with special formatting.
It all started when my wife mentioned there were a couple of meetings she had to schedule at work, one on the second Tuesday of every month and the other on the third Tuesday of every month. That got me to thinking about how to create a formula in Excel to calculate those dates.
After getting a partial solution, other things … Read the rest