Store and retrieve multiple values in one cell

[Mrock1]

Thought I'd share this.
I have been looking for a way to store multiple values in a single cell while having that cell's value be and display the first, or primary value. I found a lot of ideas that just didn't work but I finally came up with my own that does.

In the VBE add a module and creat the following function.
function eval(Byval Target as Range)
eval = application.evaluate(Target.Function)
end function

In your spreadsheet enter an array constant in the cell you wish to hold multiple values: "={4,3,2}" without the quotes but with the curly braces and do not use Ctrl-Shift-Enter as you would if entering an array function. Let's assume cell A1.

The cell will display the value 4 and return that if the cell is used in any formula normally.

To retrieve the other values, or the first explicitly, use the function
=index(eval(A1), 1, n) ;where n can be 1,2, or 3 to return 4,3, or 2 respectively.

I'm sure the above eval function could be made sexier to result in an error if the array bounds are exceeded or other circumstances and that this concept could easily extend to multi-dimensional arrays. However this simple form met my objectives as I was trying to store a risk rating and the corresponding likelihood and impact values from a risk matrix.

 

[mole999]

interesting concept, what happens when you want to change risk values, are you stuck having to edit all the formulas ?

 

[Peter_SSs]

Since this thread seems purely about Excel, I have moved it to the Excel Questions forum.

Thought I'd share this.
I have been looking for a way to store multiple values in a single cell while having that cell's value be and display the first, or primary value. I found a lot of ideas that just didn't work but I finally came up with my own that does.

In the VBE add a module and creat the following function.
function eval(Byval Target as Range)
eval = application.evaluate(Target.Function)
end function

In your spreadsheet enter an array constant in the cell you wish to hold multiple values: "={4,3,2}" without the quotes but with the curly braces and do not use Ctrl-Shift-Enter as you would if entering an array function. Let's assume cell A1.

The cell will display the value 4 and return that if the cell is used in any formula normally.

To retrieve the other values, or the first explicitly, use the function
=index(eval(A1), 1, n) ;where n can be 1,2, or 3 to return 4,3, or 2 respectively.

I'm sure the above eval function could be made sexier to result in an error if the array bounds are exceeded or other circumstances and that this concept could easily extend to multi-dimensional arrays. However this simple form met my objectives as I was trying to store a risk rating and the corresponding likelihood and impact values from a risk matrix.

I'm not really sure why you would want to have a set-up like that but ..

a) Shouldn't that .Function I have highlighted above be .Formula

b) Here is another variation, using the same example. This example is for numerical values but the concept can be applied to strings or combinations and to multiple dimensions as above.

In A1** put the formula =4+N(" 4 3 2 ")

In the sheet use the formula =Extr(A1,2)

The UDF is

Function Extr(rng As Range, num As Long) As Double
  Extr = Split(rng.Formula)(num)
End Function

** If you didn't want to display anything in A1, the formula could be =MID(N(" 4 3 2 "),2,1)

 

[Mrock1]

Hi,

To respond to both mole999 and PeterSSs:

To both - Acorrection (it musty have been late when I posted this). The eval = Application.Evaluate(Target.Function) should, as Peter suggests, read eval = Application.Evaluate(Target.Formula)

mole999
Yes to change a value you would need to edit the formula, however this could be done using Worksheet_change as is my current intent. I either want the operator to select a risk rating from a popup matirx, from which we would know the likelihood and impact values, or they could enter just a risk rating (say 1-4, representing, say, insignificant, moderate, high and extreme, then using the worksheet_change formula determine the apporpriate corresponding likelihood and impact values to build and store the required array constant "formula". At least we now have a cell with the 3 values stored.


Peter - Thanks for the alternate idea as I was hoping my notion would inspire others to refine the process. However, my UDF does permit a combination of numeric and string values such as ={4, "Cat", 3, "Dog"}.

Sorry for putting this in the wrong forum. I wasn't quite sure if it should go in the Excel Questions as it's not really a question

 

[Mrock1]

Just one more thing, following on from Peter's last snippet. IUf you want a null value and nothing to display in the cell, enter the array "={"",3,2}

 

[Peter_SSs]

However, my UDF does permit a combination of numeric and string values such as ={4, "Cat", 3, "Dog"}.

Firstly, I'm not suggesting my way is particularly better, just different.

As I mentioned, if text and/or numeric may be required, my basic method could be adapted.
I've changed my cell formula delimiter to be something less likely than a space to appear in a string value being used.
(I've also used a different function name here just so I can be testing them all in my sheet)


Cell A1: =4&MID(N("|4|small cat|3|dog|"),2,1)

[or if text is the primary value ="dog"&MID(N("dog|3|small cat|4"),2,1)]

udf:

Function ExtrV(rng As Range, num As Long) As Variant
  Dim v As Variant
  
  v = Split(rng.Formula, "|")(num)
  If IsNumeric(v) Then v = CDbl(v)
  ExtrV = v
End Function

Just one more thing, following on from Peter's last snippet. IUf you want a null value and nothing to display in the cell, enter the array "={"",3,2}

But if you were doing this just to hide your real values of 3 and 2, your INDEX formula would be a bit trickier as you would have to use 2 as the final argument in your formula to extract the real first value of 3.

 

[Mrock1]

Hi Peter,

As said, I had hoped others would chime in with other good ideas.

 

[Peter_SSs]

Hi Peter,

As said, I had hoped others would chime in with other good ideas.

Wouldn't another good idea be to just put each item in a separate cell and use a standard INDEX function?
Are you able to explain or give an example of a circumstance that has an advantage to putting them in a single cell?

 

[Mrock1]

Yes it would, to an extent, but the workbook I wish to use this in has an existing and quite complex strcuture, with underlying macros that I'd need to modify if I were to add 2 new coilums, so I'm trying to work within the limited realestate. Besides which, it's the record of the risk rating that truly matters and the likelihood and impact values are wanted so that the risk ranting could be amended in the future should the risk matrix ever change. For instance, a future refined risk martix might have more risk rating levels and the correspondiong likelihood and impact values would map to a new rating. This could be automated through various macros. Unfortunately I can't share the workbook as it is company intellectual property.

Otherwise you make a good point. Perhaps I should have thought of this in the original design

 

[mole999]

you do have 16 thousand columns to play with generally, so offsetting a column (=A1) in BB1 say give you plenty of room to explore, even it it is not ideal