Re - GGVT Excel To Return an Array of Values following Modification

T

TKT-Tang

Re - GGVT Excel To Return an Array of Values following Modification

In the beginning,
1. Look at a range of values entered into the worksheet cells A1:A10.

2. A10 contains "ZZ" (which is essentially unique within the scope of
the given range).

3. The following formula returns the given range of values following
modification :-
4. =IF(A1:A10=A10,"@",A1:A10) ; meaning to have replaced "ZZ" with "@"
and that the other values remain unchanged.

5. Note that it's the returning array being modified after the given
range ; otherwise, the physical contents of the range of cells
(located on the worksheet) remain intact.

And now,
1. Look at the given range A1:A10 again.

2. A10 has become an empty cell ; in addition, other cells within the
given range may or may not be empty.

3. The question is, how is it possible to modify and return the array
just like {A1,A2,A3,A4,A5,A6,A7,A8,A9,"@"} ? Meaning that a means is
to be devised in order to target A10 specifically for modification.

4. Note that the above expression of an array is given for the sake of
clarification. Pedantically speaking, Excel would not accept an array
containing variables expressed such as A1, A2 and etcetera.

5. Again, note that it's the returning array being modified after the
given range ; otherwise, the physical contents of the range of cells
(located on the worksheet) remain intact.

Regards.
 
Top