IF, INDIRECT, & Wild cards

[javablood]

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

 

[Jacob Skaria]

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

 

[javablood]

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]

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!

 

[T. Valko]

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))

 

[javablood]

Biff,

I have a spreadsheet in which data from Access is updated for graphing
purposes. The data, which are analtyical results from environmental samples
and are in concentration units, e.g., mg/l, can either be detections (values)
or nondetections (contain a "U" signifying a less than condition). The
INDIRECT("'"&$T$1&"'!" & "h"&$N37) is used to capture the data from another
worksheet without a lot of hand entry. So, yes, what comes in from Access is
a text field because I am combining a concentration field (value) and a flag
field (U).

I was trying to account for the "U" in order to ignore those cells and only
get the value of a detected chemical. I have 12 chemicals from which I need
to extract the detections and I just paste the data from Access into a new
row, go to my 'calculation' worksheet to copy/paste the the formulas and
input the new row number, e.g., now in $N38, and voila I have my data. So I
do not think the count fn will work fro me and what I need. Thanks.

 

[T. Valko]

Ok, let me explain what the formula is doing. Here's your formula:

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

Here's my suggested replacement:

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

In your formula, you're using VALUE(...) to convert a TEXT number to a
numeric number. You're using the ISERROR function to handle when the cell
contains a text character like the letter U. Basically, return whatever is
in cell H? and if it's a text number convert it to a numeric number
otherwise just return the text character.

My formula is doing *exactly* that just in a different way.

COUNT(-INDIRECT("'"&$T$1&"'!H"&$N37)) does the same thing as:

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

And

--INDIRECT("'"&$T$1&"'!H"&$N37) does the same thing as

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

In my formula:

If cell H? contains just a letter:

-INDIRECT("'"&$T$1&"'!H"&$N37)

Will return a #VALUE! error and pass that error to COUNT. COUNT ignores
errors so the count will be 0 and the IF logical test will evaluate to FALSE
and process the value_if_false argument which is simple return whatever is
in cell H?.

If cell H? contains a TEXT number like 100:

-INDIRECT("'"&$T$1&"'!H"&$N37)

The minus sign will coerce the text number to be the *numeric* number -100
and pass this value to the COUNT function. Since -100 is a true numeric
value the count will be 1 and the IF logical test will evaluate to TRUE and
process the value_if_true argument: --INDIRECT("'"&$T$1&"'!H"&$N37). Double
negation works like this:

The first minus sign coerces the text number to a negative numeric number.
The second minus sign coerces the negative numeric number to a positive
numeric number:

text 100
-100 = -100 as a numeric number
--100 = 100 as a numeric number

So, the value_if_true argument will return numeric 100.

My formula is doing the same thing you're formula is doing just in a
different way that saves a few keystrokes and uses a couple less function
calls.

 

[javablood]

Ok, thanks. More than one way to direct a cell!

 

[T. Valko]

You're welcome!

--
Biff
Microsoft Excel MVP

Ok, thanks. More than one way to direct a cell!