#N/A is not text, cannot use it in functions
 

In short: Two tables, one with serialnumbers from 0 through 100
The other with ONLY selected numbers from this range from 0-100.

I added the info from the second table into the first (in a new column) with
'VLOOKUP' ("=VLOOKUP(A100,Sheet2!A:D,3,FALSE)")
All serialnumbers which weren't in the first table, now, obviously, feature
a "#N/A" (No value available) 'marker' or 'sign' or text.
Before and after formatting this column to TEXT (after copying the whole
sheet to a new sheet and pasting 'VALUES' only), following function refuses
to work:

"=IF(J2="#N/A","",IF(J2="BT","BT",""))"

The outcome is "#N/A" instead of " " when the inputfield shows "#N/A"

There is ONE way in which I CAN get it to function, and that's by copying
the "#N/A" from J2 and pasting it into the formula, but I have to do this for
EVERY of the 36000 rows.....(No way José.....), because copying this function
down, doesn't make it work for any following #N/A's

So even though I converted it to TEXT, there is still som row-dependent
information lurking in the back which messes up my function?????

Any thoughts?

#N/A is not text, cannot use it in functions
 

=IF(ISNA(J2),"",IF(J2="BT","BT",""))"

--

HTH

RP
(remove nothere from the email address if mailing direct)


"R-P" wrote in message
...
In short: Two tables, one with serialnumbers from 0 through 100
The other with ONLY selected numbers from this range from 0-100.

I added the info from the second table into the first (in a new column)
with
'VLOOKUP' ("=VLOOKUP(A100,Sheet2!A:D,3,FALSE)")
All serialnumbers which weren't in the first table, now, obviously,
feature
a "#N/A" (No value available) 'marker' or 'sign' or text.
Before and after formatting this column to TEXT (after copying the whole
sheet to a new sheet and pasting 'VALUES' only), following function
refuses
to work:

"=IF(J2="#N/A","",IF(J2="BT","BT",""))"

The outcome is "#N/A" instead of " " when the inputfield shows "#N/A"

There is ONE way in which I CAN get it to function, and that's by copying
the "#N/A" from J2 and pasting it into the formula, but I have to do this
for
EVERY of the 36000 rows.....(No way José.....), because copying this
function
down, doesn't make it work for any following #N/A's

So even though I converted it to TEXT, there is still som row-dependent
information lurking in the back which messes up my function?????

Any thoughts?

#N/A is not text, cannot use it in functions
 

"R-P" wrote in message
...
In short: Two tables, one with serialnumbers from 0 through 100
The other with ONLY selected numbers from this range from 0-100.

I added the info from the second table into the first (in a new column)
with
'VLOOKUP' ("=VLOOKUP(A100,Sheet2!A:D,3,FALSE)")
All serialnumbers which weren't in the first table, now, obviously,
feature
a "#N/A" (No value available) 'marker' or 'sign' or text.
Before and after formatting this column to TEXT (after copying the whole
sheet to a new sheet and pasting 'VALUES' only), following function
refuses
to work:

"=IF(J2="#N/A","",IF(J2="BT","BT",""))"

The outcome is "#N/A" instead of " " when the inputfield shows "#N/A"

There is ONE way in which I CAN get it to function, and that's by copying
the "#N/A" from J2 and pasting it into the formula, but I have to do this
for
EVERY of the 36000 rows.....(No way José.....), because copying this
function
down, doesn't make it work for any following #N/A's

So even though I converted it to TEXT, there is still som row-dependent
information lurking in the back which messes up my function?????

Any thoughts?

try


=IF(ISNA(J2),"",IF(J2="BT","BT",""))

#N/A is not text, cannot use it in functions
 

Thanks you both, it did solve my problem (which I should be satisfied with)
but it didn't answer my question what is wrong with treating it as text.

I had to copy the '#N/A' values from e.g. row 10 to get row 1 to work (row 1
was the row where I had copied the value from to use in the function).
So by copying a value I changed its essence (isn't that kind of like the
Heisenberg principle???)

So an answer to this twilight-zone behaviour would still be welcome... :)
But for now I'm happy that I can go on with my work and that I eliminated
about a 1000 out of the 35000 items to be manually investigated thanks to
your formulas...

"Lee Harris" wrote:


"R-P" wrote in message
...
In short: Two tables, one with serialnumbers from 0 through 100
The other with ONLY selected numbers from this range from 0-100.

I added the info from the second table into the first (in a new column)
with
'VLOOKUP' ("=VLOOKUP(A100,Sheet2!A:D,3,FALSE)")
All serialnumbers which weren't in the first table, now, obviously,
feature
a "#N/A" (No value available) 'marker' or 'sign' or text.
Before and after formatting this column to TEXT (after copying the whole
sheet to a new sheet and pasting 'VALUES' only), following function
refuses
to work:

"=IF(J2="#N/A","",IF(J2="BT","BT",""))"

The outcome is "#N/A" instead of " " when the inputfield shows "#N/A"

There is ONE way in which I CAN get it to function, and that's by copying
the "#N/A" from J2 and pasting it into the formula, but I have to do this
for
EVERY of the 36000 rows.....(No way José.....), because copying this
function
down, doesn't make it work for any following #N/A's

So even though I converted it to TEXT, there is still som row-dependent
information lurking in the back which messes up my function?????

Any thoughts?

try


=IF(ISNA(J2),"",IF(J2="BT","BT",""))