Learn Excel from Bill Jelen with this daily 2 minute video podcast.
A viewer from Portugal sends in a question about finding which row contains the match. Episode 1139 shows you a few ways to go!
posted by Bill at 9:00 AM
Today's Dueling Excel Podcast is a question sent in by Hassan. Hassan wants to enter a name and a through-month and then add up that person's sales from January through the selected month. Mike and Bill show various methods in Episode 1134.
posted by Bill at 9:00 AM
Julian wants to point to the row above the current row. This formula works fine until he deletes a row then those formulas change to #REF! errors. Julian asks how to point to "the row above" without specifying a row number. Episode 1061 shows you how.
posted by Bill at 9:00 AM
Ally asks how to have a chart automatically extend as you type new data. The classic solution is to use a pair of dynamic named ranges using OFFSET. While this can be painful, Episode 993 shows how to set it up. Be sure to watch Episode 994 tomorrow for an easier way in Excel 2003-2007.
Labels: charting, Dynamic, Dynamic Range, Offset
posted by Bill at 9:00 AM
An alternative to Episode 889...If you need to match two sorted columns with a VLOOKUP, you can make the table array be dynamically calculated with the OFFSET function. Episode 890 shows you how.
posted by Bill at 9:00 AM
Marc sends in an interesting question about SUMIF. Each time the value is equal to January, he wants to grab a five-cell range! SUMIF doesn't seem up to the challenge, but an array formula with OFFSET does the trick. Episode 842 shows you how.
Labels: array formula, Offset, SUMIF
posted by Bill at 9:00 AM
Jonathan is frustrated that his validation scrollbars start in the midst of several blank cells that he set up in his validation list. He was doing this to allow room for future growth in the list.
Labels: Dynamic Range, Offset, Validation
posted by Bill at 9:00 AM
These two functions allow you to dynamically point to a cell or a range. While they are often useless on their own, they can be used as arguments to another function. Episode 485 introduces these two functions.
posted by Bill at 9:00 AM
Joe writes in with a question of how to subtract the most second most recent price in the row from the most recent price. The solution involves the relatively obscure OFFSET function. Episode 455 shows you how.
Labels: Offset
posted by Bill at 9:00 AM
