IF, INDIRECT, & Wild cards
Try the below instead
=IF(ISERROR(SEARCH("u",INDIRECT("'"&$T$1&"'!" &
"h"&$N37))),VALUE(INDIRECT("'"&$T$1&"'!" & "h"&$N37)), INDIRECT("'"&$T$1&"'!"
&
"h"&$N37))
If this post helps click Yes
---------------
Jacob Skaria
"javablood" wrote:
I have the following formula in a worksheet cell that captures data from
another worksheet:
=IF(INDIRECT("'"&$T$1&"'!" & "h"&$N37) = "*U*", INDIRECT("'"&$T$1&"'!" &
"h"&$N37), VALUE(INDIRECT("'"&$T$1&"'!" & "h"&$N37)))
This works great if there is no "U" in the referred to cell because a number
is returned but if there is a "U" in the referred to cell, #VALUE! is
returned!
If I just use =INDIRECT("'"&$T$1&"'!" & "h"&$N37), it works fine whether
there is a "U" or not in the referred to cell, e.g., 10U will be returned but
a 50 will be returned as text and I need a number if there is no "U". Hence,
my need for the IF statement. The referred to cells come from an Access
database query where I had to combine two fields to create one field for
Excel.
Does anyone have an idea of what I may be doing wrong?
TIA
--
javablood
|