Can this be made to work? Array constant in one cell
Edit mode press F9 F9 does work, but not in the sheet.
Trying to avoid macros and vba and named array (need too many)
What I want to do here does work if this is entered into a name as reference to: so that is not an answer I am looking for.
I am looking for an answer that can do maths on 2 x 99 array constants entered in single cells.
Tried with INDIRECT, I'm sure there's something I am forgetting here, to do with being able to make formulae construct themselves.
Here's the typing:
{5,4,3,2,3,4,5;1,2,3,4,5,6
=CONCATENATE("=SUM(INDEX("
and the result of the concatenate (which does work in itself)
=SUM(INDEX({5,4,3,2,3,4,5;
How to get that array constant indirectly into the formula?
Anthony
Hello,
use the Evaluate Formula tool to see what the parts of the formula return and how it all comes together. Evaluate Formula is invaluable when working with arrays.
The formula ...
=SUM(INDEX({5,4,3,2,3,4,5;
... sums the second column of each row in the array, i.e. 4 + 2
cheers, teylyn
use the Evaluate Formula tool to see what the parts of the formula return and how it all comes together. Evaluate Formula is invaluable when working with arrays.
The formula ...
=SUM(INDEX({5,4,3,2,3,4,5;
... sums the second column of each row in the array, i.e. 4 + 2
cheers, teylyn
Indirect() will evaluate a text string and return a range. Indirect() will do NOTHING to evaluate the contents of a cell, be it an array constant or a novel.
You may want to step back a bit, do some more research and check your premises.
You're asking questions that are based on assumptions that are way off. Excel does not work that way, even if you want it to.
Please do some reading about arrays and array formulas (and their limitations) and try to get a better understanding. Your premises are so far off the mark that it is difficult to suggest a workable solution. You seem to be fixed on this "multiple values in one cell as an array, which can be evaluated by a formula" approach. No. It does not work this way. Otherwise, people and businesses would not be spending big bucks to develop database solutions.
42!
You may want to step back a bit, do some more research and check your premises.
You're asking questions that are based on assumptions that are way off. Excel does not work that way, even if you want it to.
Please do some reading about arrays and array formulas (and their limitations) and try to get a better understanding. Your premises are so far off the mark that it is difficult to suggest a workable solution. You seem to be fixed on this "multiple values in one cell as an array, which can be evaluated by a formula" approach. No. It does not work this way. Otherwise, people and businesses would not be spending big bucks to develop database solutions.
42!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Note: I agree with teylyn, this isn't really a viable option for any robust long-term solution, more of a curiosity....but see attached for demo of above
barry
Evaluate.xls
barry
Evaluate.xls
ASKER
The solution is beyond excellence: it works.
ASKER
Very interesting, so it can be done, after a fashion (meaning macros through a sort of back door).
A UDF was where I expected this to go, in fact isn't that what this is....
Amazing what can be achieved when one does not know something cannot be done.
I noticed that the C API has been mapped to the old XLM macro system, something like that, so these old macros may stay around for the foreseeable future.
Anthony
A UDF was where I expected this to go, in fact isn't that what this is....
Amazing what can be achieved when one does not know something cannot be done.
I noticed that the C API has been mapped to the old XLM macro system, something like that, so these old macros may stay around for the foreseeable future.
Anthony
ASKER
this is for my future reference:
http://www.ozgrid.com/foru
http://www.ozgrid.com/foru
The VBA help topic for EVALUATE is a bit vague and probably doesn't do it justice. In fact, after reading through it for yourself, you probably wouldn't see much cause to use it. Here's what it can do:
1. Converts string math expressions to values.
2. Converts 1D and 2D string arrays to their array equivalents.
3. Capable of processing any formula a worksheet cell can process!
That's right, it can do anything a cell can do! It contains all the functionality of a worksheet cell wrapped in a single VBA command. In fact, it can even do one thing that cells can't do... it can return whole arrays. So it's like having free access to a worksheet cell... only it's better than a worksheet cell in the sense that it can evaluate and return arrays.
1. Converts string math expressions to values.
2. Converts 1D and 2D string arrays to their array equivalents.
3. Capable of processing any formula a worksheet cell can process!
That's right, it can do anything a cell can do! It contains all the functionality of a worksheet cell wrapped in a single VBA command. In fact, it can even do one thing that cells can't do... it can return whole arrays. So it's like having free access to a worksheet cell... only it's better than a worksheet cell in the sense that it can evaluate and return arrays.
You can, however, send this to a UDF for processing as desired