Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Evaluate "nested-merge" function?
Hi everyone,
I'm using a swedish version so I don't know the exact name of the function I used, but it "merged" the values of cell references that I inputed. A1=Left A2=B1 A3=4 So, I write the function (translated to english): =MERGE("=";A1;"(;";A2;";";A3;")") Which then reads: =Left(B1;4) Now, the big question... is it possible to evaluate this function directly? Alternatively I need to copy / paste value to have it evaluated, but this feels unnecessary cumbersome.. I'm sure there's a more clever approach - I just need someone to point it out to me : Kindly, Mikael Sweden |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Evaluate "nested-merge" function?
I think the function you are referring to is CONCATENATE.
To evaluate the composite string you have built up as if it were a formula, you could use this UDF: Function Eval(Rng As Range) As Variant Application.Volatile True Eval = Evaluate(Rng.Text) End Function Then assuming that your formula is in A4, put this in A5: =Eval(A4) Hope this helps. Pete On Oct 31, 1:16*pm, Mikael Lindqvist wrote: Hi everyone, I'm using a swedish version so I don't know the exact name of the function I used, but it "merged" the values of cell references that I inputed. A1=Left A2=B1 A3=4 So, I write the function (translated to english): =MERGE("=";A1;"(;";A2;";";A3;")") Which then reads: =Left(B1;4) Now, the big question... is it possible to evaluate this function directly? Alternatively I need to copy / paste value to have it evaluated, but this feels unnecessary cumbersome.. I'm sure there's a more clever approach - I just need someone to point it out to me : Kindly, Mikael Sweden |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Evaluate "nested-merge" function?
Before this answer I never thought about UDF, so this opens up a new range of
possibilities and for that I'm very grateful to you! : However, in this particular case the supplied UDF returns a standard "value error". (The function I supplied elow was a simplified example of a somewhat more complicated function, but I assume the princiiple is the same?). Anyhow, if I copy and paste the function (special - value) it evaluates (after I focus and enter the cell). So the function I managed to retrieve using a "hairy" concatenation is correct. I also tried to name the range that it evaluates to "Rng", if that has something to do it with it (I noticed the "Rng As Range..." in your code)?? Kindly, Mikael "Pete_UK" wrote: I think the function you are referring to is CONCATENATE. To evaluate the composite string you have built up as if it were a formula, you could use this UDF: Function Eval(Rng As Range) As Variant Application.Volatile True Eval = Evaluate(Rng.Text) End Function Then assuming that your formula is in A4, put this in A5: =Eval(A4) Hope this helps. Pete On Oct 31, 1:16 pm, Mikael Lindqvist wrote: Hi everyone, I'm using a swedish version so I don't know the exact name of the function I used, but it "merged" the values of cell references that I inputed. A1=Left A2=B1 A3=4 So, I write the function (translated to english): =MERGE("=";A1;"(;";A2;";";A3;")") Which then reads: =Left(B1;4) Now, the big question... is it possible to evaluate this function directly? Alternatively I need to copy / paste value to have it evaluated, but this feels unnecessary cumbersome.. I'm sure there's a more clever approach - I just need someone to point it out to me : Kindly, Mikael Sweden |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Evaluate "nested-merge" function?
However, in this particular case the supplied UDF returns a standard "value
error". (The function I supplied elow was a simplified example of a somewhat more complicated function, but I assume the princiiple is the same?). Perhaps there is a mistake in your complex formula that is causing the VALUE error. It's always easier to help on the actual formula, so you should post it if possible. I might point out that you have an extra ; in the short formula above, which causes a VALUE error. I don't know if that is just a typo here or back in Excel, too. Look close, this is different in the 3rd argument: =MERGE("=";A1;"(";A2;";";A3;")") |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Text "comparison" operator for "contains" used in an "IF" Function | Excel Worksheet Functions | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
Change the "Insert Function" display for mail merge | Excel Worksheet Functions | |||
Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel | |||
"IF" function - 7 nested limit | Excel Worksheet Functions |