Storing an array in a cell

[Blue Max]

We can enter an array in a cell as a formula such as ={"Right","Wrong"}.
The cell will only display the first value of the array. So far, so good.
However, when we attempt to access this array we are not having much
success.

For example, if the array is stored in cell A1, we place the formula
=INDEX(A1,1,2) in cell A2. We assumed that the the formula in A2 would
display the word "Wrong" which is the second element of the array as
specified in the INDEX() function. Instead, we get the #REF error.

Any thoughts on how we can use a formula to create an array result, store
the array in a cell, and then access the array from the cell and use it in a
formula that will accept an array argument?

 

[T. Valko]

Hmmm...

I've never thought to do that but I'm not having any luck getting it to
work.

You can use a named array constant.

Insert>Name>Define
Name: array
Refers to: ={"Right","Wrong"}
OK

=INDEX(array,1,2) = Wrong

What's wrong with just using separate cells to hold the array?

A1 = Right
A2 = Wrong

 

[Bernd P]

Hello,

One solution:
1. Enter into A1:
'{"Right","Wrong"}
2. Press ALT + F11, insert a new macro module and copy EVAL function
from http://www.decisionmodels.com/calcsecretsh.htm
3. Go back to spreadsheet and enter into A2:
=INDEX(EVAL(A1),1,2)

Regards,
Bernd

 

[Blue Max]

Thanks, Bernd, we appreciate the link to the EVAL function. We will give it
a try.

Nevertheless, we are still a little perplexed. It was our impression,
generally speaking, that functions retrieved the actual cell contents from a
cell reference, in this case an array. So do you have any idea why the
INDEX() function doesn't recognize the contents of the cell reference as an
array for the first argument?

Thanks,
Richard

*****************

 

[Blue Max]

Hello Biff,

Thanks for the reply and for the suggestion regarding the defined name.
Fortunately, the defined name appears to do what the cell reference cannot.
Unfortunately, the defined name approach probably would not be practical to
our application.

Why not use separate cells, you ask? Well, we could and, in fact, could
even use separate cells and no array at all. But then, we are curious
little souls, and the possibility of passing multiple data simultaneously
intrigued our imagination a little. Furthermore, we have worked on another
project where we wanted to use a worksheet formula to construct an array
(within the formula) and then use that array data to finish calculating a
result for the formula. Unfortunately, we never did fully resolve that
problem, without third-party functions, and thought that this might be a
clue to solving some of our prior array issues.

In our case, we envisioned some potential space efficiencies if we could
transmit multiple data through one cell. Our application included a
color-coded work schedule where we needed to count the cells of a certain
color in each column. However, we also wanted to count the cells of that
color that were actually staffed by an employee versus those that were not.
This information would help us summarize how many employees we needed for
that color-coded task versus how many we actually had assigned to the task.
Moreover, some of this information could be displayed in a single cell, much
as you would do if summarizing "1 of 5" total messages read in a single
cell, for example.

Of course, we have many color-codes and many time frames summarized in a
large grid-work. Working with an array, we hoped to be able to calculate,
display, and then reuse multiple types of data transmitted through one cell.
It looked promising initially, except that other functions don't seem able
to convert the cell reference to the array content as one might imagine.

Thanks,
Richard

*************

 

[Bernd P]

Hello,

Its simply because A1 does not contain your array, just the first cell
of it.

In my example A1 is containing a string which represents the whole
array but it has to be extracted/interpreted later on...

Regards,
Bernd

 

[Blue Max]

Thanks, Bernd, we understand. However, still wish they had a worksheet
function that would allow us to indirectly reference the array stored in the
cell versus the modified cell contents.

*****************

 

[Lori Miller]

Steve Dalton (expert and author of books on Excel development) has confirmed
this is not possible within the Excel API:

'...an array cannot be a single cell's type. Excel will always
convert references or arrays to single values in single cells.'

http://groups.google.com.au/group/m...89f4/46b647b8ef8f5815?lnk=gst&q=array+in+cell

You could however do this indirectly by referring to the cell's formula
using a defined name formula:

Array_Val =EVALUATE(MID(GET.CELL(6,$A$1),2,255))

then in a cell enter e.g. =INDEX(Array_Val,2) for the second element of the
array.

 

[Dave Peterson]

You're storing a formula in the cell. That means you'll have to use VBA or one
of those XLM macros/names to retrieve the formula. Then parse it the way you
want.

And if you're doing that, why bother. Just store it as a string.

ps.

debug.print ActiveCell.Value
returns
Right

So the value isn't an array--the formula is, the value isn't.

 

[Blue Max]

Thank you, Lori, very helpful.

**************

Steve Dalton (expert and author of books on Excel development) has
confirmed
this is not possible within the Excel API:

'...an array cannot be a single cell's type. Excel will always
convert references or arrays to single values in single cells.'

http://groups.google.com.au/group/m...89f4/46b647b8ef8f5815?lnk=gst&q=array+in+cell

You could however do this indirectly by referring to the cell's formula
using a defined name formula:

Array_Val =EVALUATE(MID(GET.CELL(6,$A$1),2,255))

then in a cell enter e.g. =INDEX(Array_Val,2) for the second element of
the
array.