Forum Discussion
Excel EVAL() function does not support structured table references
I sometimes create complex lambda() to dynamically create a function string intended for EVAL(). The latest use required structured table references within that dynamically created function string. Unfortunately, it appears that EVAL() does not support structured table references.
My function is much more complex than this, but the following is an easy way to recreate the issue.
In a column of a table try the following
Succeeds: =[@column]
Fails: =EVAL("[@column]")
I'm wondering why EVAL doesn't work on any function equally. And also if there is a workaround for evaluating a dynamically constructed formula using structured table references.
6 Replies
nicee
maericson Try either =EVAL([@Column]) or =EVAL("Table1[@Column]"), depending on the desired outcome.
- Did you try your suggestion? Neither work, they result in the same #NAME? error.
maericson Tested and proven to work, with different outcomes...
=EVAL([@Column])
=EVAL("Table1[@Column]")
EVAL: =LAMBDA(x,EVALUATE(x))The question is, how are you using the EVAL function? You still have not yet provided any sample data, or a legitimate example of the syntax you are attempting.
- wondering why EVAL doesn't work on any function equally.
This is the true.
I guess there maybe another way if share some data.- In short, I am trying to create some data-driven calculations. I am importing multiple generated CSV files using "Get Data (Power Query)" regularly. The CSV format might change (added columns, order of columns) so originally I was building the calculations by hand for each sheet/csv-import using structured table references.
That became very tedious when adding CSV input, or when there were changes needed in the formula. So, I came up with the idea of having some 'metadata' for each sheet from which I can dynamically create the formula and only need to update the column names in that metadata.
It all works great except that there seems to be a bug or limitation in EVAL(). My best workaround for now is to copy and paste the dynamically generated formula into the sheets.
I suppose I could dynamically generate a formula with match and index, but that would be unfortunate given the covenience of structured table references.
