wmfexcel | Work smarter by Mastering Functions in Excel

A decade ago, I wrote a blog post about the same in an no-formula approach. Ten years has past and Excel has evolved so much. With Excel 365, the same task can be performed in an efficient and effective manner by using TEXTJOIN function. See screenshot...

by hiding all unwanted columns and rows
Did you know there are 16384 columns and 1048576 rows on a worksheet? We have the flexibility to navigate any cells within this large range! Nevertheless, this is NOT what we want some time. Think about when you have created a wonderful report or dashboard...

Again, a picture tells a thousand words. The following GIF shows what we want to achieve:
Seriously? A simple VLOOKUP formula stated below would do. What’s the challenge?
Yes, you are right if all these tables reside on the same worksheet. What if they are sitting on different worksheets? or even on different columns?...

Quite a long time ago, I wrote about two different approaches of preparing dependent dropdown menu in Excel. The first approach was for non Excel365 users, while the second approach was for Excel365 users. You may refer to the post here.
In this post, I will be using the same sample workbook, solving the same problem, but by a third approach using OFFSET, an Excel function that went unnoticed....