Episode 890 - Offset as TableArray

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.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Episode 842 - SumIf a Range

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.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Episode 720 - Dynamic Validation

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.

Instead, Episode 720 will show you how to set up a dynamic named list using the OFFSET function.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!