Excel’s Custom Lists are great, but you need to drag them to create them. If you have a list that you use frequently why not create a custom function to display it? This has the added advantage of creating a spill range that can then drive other dynamic array formulas.
This technique is ideal for lists that don’t change. Like days of the week (this example) or months of the year. You could use it for lists that change as well as you only need to edit the list in one place – the range name. Be careful using this technique with lists that change – the main issue may be inputs based on the list sequence. Change the lists may impact the inputs made.
If you are not familiar with custom functions check out the link below.
The LAMBDA in the image below will display a vertical list of abbreviated week names as default or if “v” is entered between the brackets. It produces a horizontal list if “h” is used between the brackets – as shown in the image below.
The custom function created is shown in the image below.
The custom function in use is shown below. The default is a vertical list.
As you can see in the image below, you can refer to the spill range to drive another calculation.
This technique offers a lot of opportunities if you work with lists.
I found this an interesting article and thought it would be “fun” to play around with your LAMBDA some. When I looked at your LAMBDA, a different method of calculating the weekday names came to mind. Here is your formula, but with my (less obvious) calculation for the weekday names array (cuts the formula length by 12 characters though)…
=LAMBDA([direction],LET(wk,DROP(TEXT(SEQUENCE(8),”ddd”),1),IF(OR(ISOMITTED(direction),direction=”v”),wk,TRANSPOSE(wk))))
Then I got the idea that the direction for the output could probably be handled completely by the SEQUENCE and DROP (required because you ended your list with Sat and Sun) functions. I needed to assign your OR function call to a variable because it would be repeatedly used 4 times within the LAMBDA, but did not know what to call it, so I just called it “x” (cuts the formula by another 4 characters, but only because the new variable’s name is only one character long).
=LAMBDA([direction],LET(x,OR(ISOMITTED(direction),direction=”v”),DROP(TEXT(SEQUENCE(7*x+1,7*(1-x)+1),”ddd”),x,1-x)))
Hi Rick
Thanks for taking an interest.
Cool, you automated the text string with SEQUENCE and then used SEQUENCE to handle the direction.
Your solutions always amaze me.
Your formula would be harder to explain.
I like “easy to explain” formulas, but admire your more elegant solutions.
Thanks again
Regards
Neale
No question it would be harder to explain, but I did hint at that by saying “less obvious”, but more importantly I said “I thought it would be fun”… and it was. My formulas were more a “could I” than a “should I”.?
Thanks Rick – The “could I” solutions can often lead to other breakthroughs. Thanks as always for sharing. ?