Using named lambda functions for data validation

[Koyaanisqatsi]

Hi there,
There are a lot of very clever data validation techniques, however, I missed the following one:

1. Problem statement: you want to restrict user input to a small number of different dynamic arrays, or not at all (i.e., free input by keyboard).
   
   
2. The dynamic arrays will be defined as named (lambda) functions, let's say: A, B, C, (entered in cells as =A, =B, and =C).
3. Choose an arbitrary home cell for the dynamic array, let's say home = $F$2, and select it.
4. Assign the "data validation by list" technique to the home cell. Disable "error message".
5. Define the source as follows (without the double quotes): " , =A, =B, =C" and save it.

That's all. Now you can select of one of the three named functions using the drop-down list in the home cell and they will create the correct
dynamic arrays (by spilling, of course). For instance, A could be a matrix of constants, B might be a matrix of random numbers, and C will be
used to "clear" the matrix, and start entering numbers (or other data) in the home cell and all other cells ("keyboard" mode).

What's strange: the source should start with "," (a comma), but it doesn't have any influence on the list of valid entries. Can anyone explain,
why there should be this comma?

 

[RoryA]

Otherwise your DV source will start with an = sign, and Excel will interpret it as a formula, not delimited text.

 

[Koyaanisqatsi]

@RoryA : Thanks for your reply, which confirms my intuition. However, it raises new questions regarding the interpretation of the source field of DV:

• Why is the "=" at the start of the source field handled differently from an "=" at later positions (once you have started with a comma)?
  
  
• Why are "=A" and "=B" handled differently from "=A & B", although all are considered text strings in a source list (without a '=' at the beginning)?

As before, A and B are the names of functions, thus strings, hence A&B is also a string. However, when choosing "=A" from the drop-down list
followed by ENTER, it executes the function A; the same for B. However, when selecting "=A&B", it executes and yields the string A&B. In fact,
I expected to get an error message, because neither is 'A&B' the name of a function (can't be so, because '&' isn't allowed in a name) nor is the
concatenation of strings A and B a valid function/formula. This is inconsistent, to say the least, and leads to time-consuming experimentation to
find a working solution for what is really a simple problem.

By the way, you may even write out a formula in the source field, preceded by the "=" sign: they will turn up in the drop-down list and be correctly
executed as long as the formula doesn't contain commas. If it does, the formula will be split up in several non-sensical strings. Putting quotation
marks around the formula string doesn't help here.