Use values from a single cell as an Array Constant?

[jodywally]

Experts,

Is there any way to make an array formula use an array constant by referencing all the value for that array constant from different, single cell?
For example, if I have a simple array formula like
{=SUM({1,2,3,4}*{1,2,3,4})} (which =30), can I store the array constant in cell A1 so that the formula would could read {=SUM(A1*{1,2,3,4})}? Right now, I store the values in A1 as ={1,2,3,4}, which Excel recognizes as an array, but the formula treats it as {1},not {1,2,3,4}, and therefore the result = 10, not 30. I've triend various formats, but unless I type the array constant directly into the formula, the values are never recognized as an array.

BTW, I know that I could define a name for the array constant, but that won't work for my purposes since I actually want to store those values in a single field of a database, return them to the worksheet via query, and then use the array formula to reference them from the resulting table.

Any ideas?

 

[mikerickson]

You could put the string {1,2,3,4} in A1 and define a name
Name: arrayInA1 RefersTo: =EVALUATE(A1)

 

[jodywally]

PGC,

I didn't make it clear enough in my original post, but I was looking for a way to process hundreds of 'embedded tables', not just one. My example of {1,2,3,4} in A1 was to simplify my question about array constants, not to define my actual project. To restate my reply to T. Valko, I have hundreds of customers, whose contracts I track in a database. Most of those contracts use a tiered pricing model - essentially a related table for each contract record.

I originally thought I might be able to store the values that define those tables as strings within fields of the contract record, and then when I refresh the table in excel, I could have a single formula fill down next to the table and use arrays to perform the pricing calculation by parsing those various strings and treating them as arrays - effectively creating a related table in memory for each contract with a tiered model.

However, I can't use strings as array constants and while I could use a named formula (or a named range) if I only had a few values always located in the same place, instead I have hundrends of values that could show up anywhere in the table, depending on the number of records in the retrieved table and the sort order (although always in the same columns). I might be able to use a variant of the {ROW(INDIRECT())} combination to parse the strings into valid arrays, but that started to exceed the KISS test, so I just added an actual related table to my database and it works great.

I'm sure you guys actually could've written a single named formula or a UDF that does what I originally wanted, but I had to move on. If you're truly interested in this as an intellectual exercise, I'd be happy to forward sample data that illustrates the problem in detail. Otherwise, I'm all set, although I do appreciate the advice.

Jody

 

[pgc01]

I'm glad your problem is solved. Thanks for the feedback.