concatenate cells by a given range
Not very practical, but just for fun and
to show that it can be done without VBA...
This simplified example concatenates the 10 numbers in set1.
set1 acc
1 1 2 3 4 5 6 7 8 9 10
2
3
4
5
6
7
8
9
10
control
3
inc
11
All ranges are named with the headers indicated.
Create three option buttons from Forms,
label them Reset, Set and Lock and
link them to the control cell.
Click the Reset button, so control shows 1.
Enter these formulas in acc and inc respectively:
=IF(control=1,INDEX(set1,1),IF(control=2,acc&" "&INDEX(set1,inc),acc))
=IF(control=1,2,IF(control=2,inc+1,inc))
Tools Options Calculation Max Iterations 9
Click Set, then Lock.
|