Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
#N/A problem
hi, i have a small problem with this formula
=IF(VLOOKUP(IF(ISERROR(SEARCH("(",A2)),A2,TRIM(LEF T(A2,SEARCH("(",A2)-1))),PLANNING!A:B,2,FALSE)=TEXT(B2,"nnnn"),"YES"," NO") if i dont write anything, the formula show me #N/A. i dont want formula to show me anything. not even zero. the cell with the formula to be blank....until i write something in my table. i mean the formula to work after i write something in my table thanks in advance |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
#N/A problem
try =if(isna(vlookup...................as long as you dont exceed 6 arguments you should be fine "puiuluipui" wrote: hi, i have a small problem with this formula =IF(VLOOKUP(IF(ISERROR(SEARCH("(",A2)),A2,TRIM(LEF T(A2,SEARCH("(",A2)-1))),PLANNING!A:B,2,FALSE)=TEXT(B2,"nnnn"),"YES"," NO") if i dont write anything, the formula show me #N/A. i dont want formula to show me anything. not even zero. the cell with the formula to be blank....until i write something in my table. i mean the formula to work after i write something in my table thanks in advance |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
#N/A problem
Hi
One way =IF(A2="","", IF(COUNTA(Planning!A:B)<2,"", IF(VLOOKUP(IF(ISERROR(SEARCH("(",A2)),A2, TRIM(LEFT(A2,SEARCH("(",A2)-1))), Planning!A:B,2,FALSE)=TEXT(B2,"nnnn"),"YES","NO")) ) Formula deliberately broken at various points to avoid NG Reader making breaks at inconvenient places. It is all one line in reality. -- Regards Roger Govier "puiuluipui" wrote in message ... hi, i have a small problem with this formula =IF(VLOOKUP(IF(ISERROR(SEARCH("(",A2)),A2,TRIM(LEF T(A2,SEARCH("(",A2)-1))),PLANNING!A:B,2,FALSE)=TEXT(B2,"nnnn"),"YES"," NO") if i dont write anything, the formula show me #N/A. i dont want formula to show me anything. not even zero. the cell with the formula to be blank....until i write something in my table. i mean the formula to work after i write something in my table thanks in advance |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
#N/A problem
Assuming that your equation works (except for the #na)
=if(isna(IF(VLOOKUP(IF(ISERROR(SEARCH("(",A2)),A2, TRIM(LEFT(A2,SEARCH("(",A2)-1))),PLANNING!A:B,2,FALSE)=TEXT(B2,"nnnn"),"YES"," NO"),"",VLOOKUP(IF(ISERROR(SEARCH("(",A2)),A2,TRIM (LEFT(A2,SEARCH("(",A2)-1))),PLANNING!A:B,2,FALSE)=TEXT(B2,"nnnn"),"YES"," NO")) -- Wag more, bark less "puiuluipui" wrote: hi, i have a small problem with this formula =IF(VLOOKUP(IF(ISERROR(SEARCH("(",A2)),A2,TRIM(LEF T(A2,SEARCH("(",A2)-1))),PLANNING!A:B,2,FALSE)=TEXT(B2,"nnnn"),"YES"," NO") if i dont write anything, the formula show me #N/A. i dont want formula to show me anything. not even zero. the cell with the formula to be blank....until i write something in my table. i mean the formula to work after i write something in my table thanks in advance |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
#N/A problem
Thanks allot!
It's working! Thanks! "Roger Govier" a scris: Hi One way =IF(A2="","", IF(COUNTA(Planning!A:B)<2,"", IF(VLOOKUP(IF(ISERROR(SEARCH("(",A2)),A2, TRIM(LEFT(A2,SEARCH("(",A2)-1))), Planning!A:B,2,FALSE)=TEXT(B2,"nnnn"),"YES","NO")) ) Formula deliberately broken at various points to avoid NG Reader making breaks at inconvenient places. It is all one line in reality. -- Regards Roger Govier "puiuluipui" wrote in message ... hi, i have a small problem with this formula =IF(VLOOKUP(IF(ISERROR(SEARCH("(",A2)),A2,TRIM(LEF T(A2,SEARCH("(",A2)-1))),PLANNING!A:B,2,FALSE)=TEXT(B2,"nnnn"),"YES"," NO") if i dont write anything, the formula show me #N/A. i dont want formula to show me anything. not even zero. the cell with the formula to be blank....until i write something in my table. i mean the formula to work after i write something in my table thanks in advance |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
#N/A problem
Roger, it' working, but can this formula to show me beside "yes" , "no" ,
another thing? i mean when the formula cant find no data or anything written in PLANNING!... to show me some kind of error? to show me "no planning" ...or what i will write in the code... thanks allot "Roger Govier" a scris: Hi One way =IF(A2="","", IF(COUNTA(Planning!A:B)<2,"", IF(VLOOKUP(IF(ISERROR(SEARCH("(",A2)),A2, TRIM(LEFT(A2,SEARCH("(",A2)-1))), Planning!A:B,2,FALSE)=TEXT(B2,"nnnn"),"YES","NO")) ) Formula deliberately broken at various points to avoid NG Reader making breaks at inconvenient places. It is all one line in reality. -- Regards Roger Govier "puiuluipui" wrote in message ... hi, i have a small problem with this formula =IF(VLOOKUP(IF(ISERROR(SEARCH("(",A2)),A2,TRIM(LEF T(A2,SEARCH("(",A2)-1))),PLANNING!A:B,2,FALSE)=TEXT(B2,"nnnn"),"YES"," NO") if i dont write anything, the formula show me #N/A. i dont want formula to show me anything. not even zero. the cell with the formula to be blank....until i write something in my table. i mean the formula to work after i write something in my table thanks in advance |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
#N/A problem
Hi
=IF(A2="","", IF(COUNTA(Planning!A:B)<2,"No Planning Data", IF(VLOOKUP(IF(ISERROR(SEARCH("(",A2)),A2, TRIM(LEFT(A2,SEARCH("(",A2)-1))), Planning!A:B,2,FALSE)=TEXT(B2,"nnnn"),"YES"," -- Regards Roger Govier "puiuluipui" wrote in message ... Roger, it' working, but can this formula to show me beside "yes" , "no" , another thing? i mean when the formula cant find no data or anything written in PLANNING!... to show me some kind of error? to show me "no planning" ...or what i will write in the code... thanks allot "Roger Govier" a scris: Hi One way =IF(A2="","", IF(COUNTA(Planning!A:B)<2,"", IF(VLOOKUP(IF(ISERROR(SEARCH("(",A2)),A2, TRIM(LEFT(A2,SEARCH("(",A2)-1))), Planning!A:B,2,FALSE)=TEXT(B2,"nnnn"),"YES","NO")) ) Formula deliberately broken at various points to avoid NG Reader making breaks at inconvenient places. It is all one line in reality. -- Regards Roger Govier "puiuluipui" wrote in message ... hi, i have a small problem with this formula =IF(VLOOKUP(IF(ISERROR(SEARCH("(",A2)),A2,TRIM(LEF T(A2,SEARCH("(",A2)-1))),PLANNING!A:B,2,FALSE)=TEXT(B2,"nnnn"),"YES"," NO") if i dont write anything, the formula show me #N/A. i dont want formula to show me anything. not even zero. the cell with the formula to be blank....until i write something in my table. i mean the formula to work after i write something in my table thanks in advance |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Started out as an Access problem. Now an Excel problem | Excel Discussion (Misc queries) | |||
problem with a conditional max problem | Excel Discussion (Misc queries) |