If you spend your time auditing other people’s worksheets, you’ll know that Excel users love their named ranges. I’ve run across some spreadsheets where 50 or more individual cells are given individual names. This makes auditing a spreadsheet an extremely muddy experience.
It sometimes helps to know where the named ranges are.
» More: Highlight All Named Ranges Part 1
About a week ago, a client of mine asked me if I could write him a function to pull data from the previous worksheet. Upon further interrogation, I found out that he has a workbook where he manages his monthly budget. Each tab consists of a month’s data. The first tab is named January, the second tab is named February, etc.
What he needed was a function that would pull data from the previous month’s sheet without having to specify the sheet name. Why? Because at the end of each month, he would add another sheet and he did want to re-jigger his formulas to change sheet names.
Vinicius writes to ask:
“Dear Mr. Pig. I would like to know what the difference is between Excel’s FIND and SEARCH. Which one should I use?”
I love the respect Vinicius shows by calling me Mr. Pig. Anyway, here is your answer Mr. Vinicius:
There are two differences between Excel’s SEARCH and FIND functions.
Ok people. Repeat after me – “A color is not data”.
Every so often I get a spreadsheet from someone who thinks that colors play a major role in data management. You know – those people who happily say “I tagged those records with Yellow”. Oh really? I don’t think my version of Excel has a “Yellow” data type.
A week ago, Dick Kusleika posted an excellent article outlining some Formula Tips. I’ve already made plans to steal and use his ideas in my training. But my plagiaristic habits are not the reason for this post.
?
.
» More: Excel Defunct Defaults
I’m no Tiger Woods, but I get my share of lady fan mail. Today, I get an email from Dawn.
She asks: “Do you have an easy way to flip a range so that the bottom row is at the top and the top row is at the bottom?”
I have two methods for doing this.
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.
I’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.
Every now and then, I find myself in trying to decipher someone else’s spreadsheet. If the spreadsheet is complicated enough, the first thing I do is turn off the ‘Edit Directly in Cell’ Option.
Turning off the ‘Edit Directly in Cell’ option tells Excel to allow the editing of a formula in the formula bar only. Although this may seem like an odd thing to do, there are peripheral benefits that make this technique a useful auditing tool. Here’s what I mean:
I 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.
