Final Friday Fix: March 2022 Challenge

On the final Friday of each month, we are going to set an Excel / Power BI challenge for you to puzzle over so that you can get your “Excel fix”. Challenge your office colleagues to see who can solve the puzzle quickest. There are no prizes at this stage: you are playing for bragging rights only!

The Challenge

We have gone through >Dynamic Arrays and the value they provide to Excel, as well as the solutions we can create with them, in detail. However, as we continue to try to tackle ever more challenging challenges, we occasionally reach some hard boundaries.

We have a list of months in a year in this case. We now use filters to see specific data daily. What would you do if you wanted to discover the first visible cell after applying the filter, which is commonly done when using dynamic arrays?

For example, if someone is looking at the sale of a company in a specific country while using the filter function, the table's and reports' headings alter in line with the country name for which data is being seen.

The use of macros and VBA could be a simple solution to this problem, but it is also a well-known reality that most Excel users are unfamiliar with VBA and macros. As a result, we must also look for a solution that does not require the use of complicated instruments.

You can download the question file here.

As always, there is a requirement:

• this is a formula challenge; no Power Query / Get & Transform or VBA!

Sounds easy? Then why not have a go? We’ll publish one solution in Monday’s blog. Have a great weekend in the meantime!