Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
#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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
#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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
#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","")) |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
#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","")) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cell text based on 4 condition test | Excel Worksheet Functions | |||
Text Wrapping | Excel Discussion (Misc queries) | |||
Create a function to return text if two logical functions are true | Excel Worksheet Functions | |||
Autofitting a row | Excel Discussion (Misc queries) | |||
Read Text File into Excel Using VBA | Excel Discussion (Misc queries) |