Not sure what you're trying to do here, but...

What is the result of:

INDIRECT("'"&$T$1&"'!" & "h"&$N37)

It looks like it might be a TEXT number since you're using the VALUE
function:

VALUE(INDIRECT("'"&$T$1&"'!" & "h"&$N37))

If that's the case you might be able use this and save a few keystrokes.

=IF(COUNT(-INDIRECT("'"&$T$1&"'!H"&$N37)),--INDIRECT("'"&$T$1&"'!H"&$N37),INDIRECT("'"&$T$1&"' !H"&$N37))

--
Biff
Microsoft Excel MVP


"javablood" wrote in message
...
I think I answered my own question with this:

=IF(ISERROR(VALUE(INDIRECT("'"&$T$1&"'!" & "h"&$N37))),
INDIRECT("'"&$T$1&"'!" & "h"&$N37), VALUE(INDIRECT("'"&$T$1&"'!" &
"h"&$N37)))

Thanks for your help!
--
javablood


"javablood" wrote:

Jacob,

Brilliant! thanks.

But if I have another letter, say "J" I tried:
=IF(ISERROR(SEARCH("U",INDIRECT("'"&$T$1&"'!"&"d"& $N37))),
VALUE(INDIRECT("'"&$T$1&"'!"&"d"&$N37)),
IF(ISERROR(SEARCH("J",INDIRECT("'"&$T$1&"'!"&"d"&$ N37))),
VALUE(INDIRECT("'"&$T$1&"'!"&"d"&$N37)),
INDIRECT("'"&$T$1&"'!"&"d"&$N37)))
but it did not work, I get #VALUE. It must be something about the
ISERROR
that I do not know.

Instead of trying to account for whatever letters there may be, is there
a
way to search/detect for no letters or just a number to be able to pull
the
value from that?
--
javablood


"Jacob Skaria" wrote:

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