Nothing makes you feel like more of a monkey than manually adjusting formulas while, all along, you have the sense that you’re doing it the hard way. This is the feeling you are likely to get when manually adjusting VLOOKUP formulas.
Posts Tagged ‘Formulas’
Copying VLOOKUPs Across Multiple Columns
November 10th, 2009Protecting VLOOKUPS from Expanding Data
November 4th, 2009I'm fresh back from the Excel Power Analyst Boot Camp we had here in Dallas. If you were there, thanks for all the great tips – I had lots of fun. If you weren't there, you missed quite the event. The Spice Girls showed up to talk about their new movie: Spice Racks. It was awesome. Lesson learned eh? Next time you'll join us.
One of the topics that kept surfacing at last week's event was the use of Tables/Lists. These are auto-expanding ranges that allow anything built on top of them to keep up with your data changes (similar to the dynamic named ranges you can create by using OFFSET and COUNTA, but much simpler). Today, I'll show you how to employ Tables/Lists to protect your VLOOKUPS from breaking when you add data to your lookup range.
Using the Switch Function in Excel
August 17th, 2009I love using the Switch function in Access. It's a real timesaver in situations where complex nested IF functions are needed. Ever spend 10 minutes trying to get a nested IF formula right?
The Switch function allows for a easier method, evaluating a list of test expressions and returning the result for the first test that evaluates to true.
The basic syntax for the Switch function is: Switch2(Test1, Result1, Test2, Result2, etc…)
Here's an example.
Handling Different Data Types with One Chart
August 12th, 2009Ahh…data types; Currency, Number, Percent. What a joy to have such a diverse set of numerical variations designed to make your life hell.
Anyone who has had to handle varying numerical formats knows how tedious it can be to build a reporting structure that can handle all formats.
If you've ever built one chart for Percents, one for Currencies, and one for Numbers, then you're reading the right blog post.
Today, I'll to show you a technique that allows one chart to accurately show any data type.
Protect Worksheet Structure with Array Formulas
July 31st, 2009In the another display of passive aggressive behavior, I want to show you a trick you can use to prevent anyone from adding or deleting rows or columns by using a simple array formula.
I'm pretty sure I got this trick from Bob Umlas. Most Bob tricks, they are extremely cool, but I never seem to find situations where I can use them. But, maybe this trick will hit the spot for some of you.
Array Formula Heresy
July 22nd, 2009If you want to commit Excel heresy, tell people you don’t like Array formulas. Oh the blasphemy – the sacrilege!
The truth is I really do try to avoid using array formulas when there is a reasonable non-array alternative. The long-and-short of it is that I think array formulas are difficult to understand and too easy to break.
I’m man enough to admit that at least part of the reason I avoid array formulas is because I’m not smart enough to understand all of the array formulas I see. In fact, I have to wrap duct tape around my head before looking at some of the fancy array creations I see other gurus employ.
Creating Subtotals on Many Columns
June 24th, 2009When building Subtotals, Excel asks you to select the columns to which you would like to add subtotals. This problem is that Excel gives you a matchbook-size dialog box that allows you to select one column at a time. So if you require subtotals on many fields, you need to click, click, click your way through the list of columns one by one.
Believe me when I say that this gets old Tout de suite. » More: Creating Subtotals on Many Columns
Choosing Quarters in Excel
June 22nd, 2009There's an old quip that says dyslexic philosophers lay awake at night wondering if there is a dog.
Well I'm not dyslexic but a few things tug at my mind at night. For example: Why don't more women find me attractive? What's this stuff on my feet? And why isn't there a function in Excel that translates a date into a quarter?
That's right, there is no native function in Excel that converts a date into a quarter. Amazing, is it not?
Here is an easy technique for converting a date into a quarter using the CHOOSE function.
Outta My Way Formula Thingy!
June 11th, 2009Excel annoyance #57 – the formula thingy. Probably officially called intellisense, this tool tip is designed to give you the arguments needed for a formula. In the example you see here, you'll notice I'm trying to enter an INDEX Formula. The formula thingy pops up right in the way of the cells I need to click in order to select my arguments.
» More: Outta My Way Formula Thingy!
Using Arrows to Edit a Named Range
June 10th, 2009So there you are, editing a named range in like the Excel superstar you are, when you make the mistake of hitting the left arrow key to go back a few characters in your formula. Excel instantly references the cell to the left. Argh!
