Hi - Dave here.
Happy Friday!
This week, I worked on a geeky formula that uses a lot of functions to do something really simple: it displays the currently defined range of a named range or table.
This is one of those Excel problems where you start off thinking "this will be easy" and then the hours go by....If you work in Excel a lot, I'm sure you've been there before :)
Anyway, the image below shows the basic idea. The current table range is B5:D16, as reported by the formula in cell G5. As you drag to resize the table, you will see the range update. Download the workbook and try it out.
[
Download the workbook and read the full explanation]
The workbook contains (1) a formula for Legacy Excel, and (2) a formula for the current version of Excel. Both formulas use the ADDRESS function to build the references we need. As you might expect, the more modern formula is simpler and more elegant. The TAKE function gives us an easy way to get the first and last cell in a range, and the LET and LAMBDA functions let us build our own custom function. The article above contains all the details and the workbook.
This is a good example of "nesting" functions inside other functions. The functions are not complicated. However, putting them all together is a more advanced skill. The best way to develop this skill is to practice :)
Excel formulas
We maintain a list of over 500 working formulas
here.
If you need more structure, we also offer
video training.
Have a great weekend!
Dave
