Using Concatentate in Formula

[Guest]

Col A Col B Col C
0.51 <.50 No

Col A contains a formula that returns a value.

Col B is data hard coded. (I juse this to help with advanced filter)

Col C ---> trying to get a formula that would return a logic test... If A
is less than the value shown in B, then Yes, otherwise No.

I'd like to use conacatentate to incorporate the hard coded value in Col B,
because I use this Col as a reference for filtering. Any ideas? Thanks...

 

[Guest]

Try:

=IF(EVAL(CONCATENATE(A1,B1)),"yes","no")

 

[Guest]

Sweet. Thanks. Never seen that EVAL function before....

 

[Dave Peterson]

=eval() isn't built into excel.

I'm guessing that Toppers has an addin that does this evaluation.

One version of a UDF that does that evaluation:

Option Explicit
Function Eval(myStr As String) As Variant
Eval = Application.Evaluate(myStr)
End Function

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Guest]

thanks Dave... Maybe "EVAL" is native to Excel 2003? it's what I'm using...

 

[Dave Peterson]

I use xl2003, too.

Can you find it in xl2003's help?

If you type
=eval(
and click on the Fx button (to the left of the formula bar), what do you get?

 

[Guest]

A function argument box pops up with a single input field labeled "TheInput"
followed by the explanation:

"Evaluates a string as though it was a formula. Returns an array from an
array formula."

But if I click on the "help on this function" hyperlink, there is no help
box.

 

[Guest]

I have Walkenbach's Power Utility Pack installed, but there's no listing of
EVAL in that...

 

[Peo Sjoblom]

It's part of Laurent Longre's Morefunc add-in

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey

 

[Dave Peterson]

Maybe it's an undocumented feature <bg>.

Or maybe it's in another addin???

 

[MyVeryOwnSelf]

=IF(EVAL(CONCATENATE(A1,B1)),"yes","no")

Sweet. Thanks. Never seen that EVAL function before....

If I try using EVAL, Excel 2003 gives a #NAME? error report in the cell.

I do have the Analysis Tool-pack add-in activated.

 

[Peo Sjoblom]

It's part of an add-in called Morefunc by Laurent Longre

http://xcell05.free.fr/english/

it's extremely useful, more info here

http://www.rhdatasolutions.com/morefunc/



--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey

 

[Guest]

Dave,

turns out I had another addin that I forgot about called FastExcel. It
seems like FastExcel has integrated a number of freely available functions.
I haven't really used it much so I can't say much about it.

After grandly announcing the completion of a spreadsheet model, I emailed
the spreadsheet out to a few coworkers. However, the cells using EVAL did
not work because EVAL was pointing to a nonexistant install of FastExcel on
my coworker's c drives...

Your advice on adding the UDF to the workbook fixes that problem...

Really glad you provided that UDF... thanks...

Do you know where I can get other UDF codes? Seems like using add-ins
really is not a good solution when you are emailing files to others...

 

[Guest]

try adding the UDF that Dave Peterson mentioned to a module within the
workbook you are using... that fixed a similar problem I had...

 

[Gord Dibben]

EVAL also is packaged with Laurent Longre's MOREFUNC

http://longre.free.fr/english/index.html


Gord Dibben MS Excel MVP

Dave,

turns out I had another addin that I forgot about called FastExcel. It
seems like FastExcel has integrated a number of freely available functions.
I haven't really used it much so I can't say much about it.

After grandly announcing the completion of a spreadsheet model, I emailed
the spreadsheet out to a few coworkers. However, the cells using EVAL did
not work because EVAL was pointing to a nonexistant install of FastExcel on
my coworker's c drives...

Your advice on adding the UDF to the workbook fixes that problem...

Really glad you provided that UDF... thanks...

Do you know where I can get other UDF codes? Seems like using add-ins
really is not a good solution when you are emailing files to others...