#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

#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

#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

#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

#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

#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

#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