Hello

I'd like to return a yes or no response.
at the moment only the YES is working.
Can anyone give me a tip?
Thanks
Rob

=IF(A6=VLOOKUP(A6,'[Catalogue Robs Test Stock
Range.xls]Catalogue'!$1:$5000,1,FALSE),"YES","NO")

Hi,

Just do not understand what you are trying to do:

Why is there no column reference in your range_lookup I.e. how can it be
$1:$5000.

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Rob" wrote in message
...
Hello

I'd like to return a yes or no response.
at the moment only the YES is working.
Can anyone give me a tip?
Thanks
Rob

=IF(A6=VLOOKUP(A6,'[Catalogue Robs Test Stock
Range.xls]Catalogue'!$1:$5000,1,FALSE),"YES","NO")

hi, Rob !

I'd like to return a yes or no response.
at the moment only the YES is working.
Can anyone give me a tip?
Thanks
Rob

=IF(A6=VLOOKUP(A6,'[Catalogue Robs Test Stock Range.xls]Catalogue'!$1:$5000,1,FALSE),"YES","NO")

AFAIK, vlookup will try to find your lookup_value in the first column of your lookup_table
so, it doesn't make sense to incude whole columns by reference of the entire rows -?-

try with:

=if(countif('[Catalogue Robs Test Stock Range.xls]Catalogue'!$A$1:$A$5000,A6),"YES,"NO")

hth,
hector.

I must have the found the most complicated way to half do it.
Thanks Miguel that works a treat.

"Héctor Miguel" wrote:

hi, Rob !

I'd like to return a yes or no response.
at the moment only the YES is working.
Can anyone give me a tip?
Thanks
Rob

=IF(A6=VLOOKUP(A6,'[Catalogue Robs Test Stock Range.xls]Catalogue'!$1:$5000,1,FALSE),"YES","NO")

AFAIK, vlookup will try to find your lookup_value in the first column of your lookup_table
so, it doesn't make sense to incude whole columns by reference of the entire rows -?-

try with:

=if(countif('[Catalogue Robs Test Stock Range.xls]Catalogue'!$A$1:$A$5000,A6),"YES,"NO")

hth,
hector.

Note that COUNTIF requires the source file be open. An alternative that
works whether the source file is open or closed:

=IF(COUNT(MATCH(A6,'[Catalogue Robs Test Stock
Range.xls]Catalogue'!$A$1:$A$5000,0)),"YES,"NO")


--
Biff
Microsoft Excel MVP


"Rob" wrote in message
...
I must have the found the most complicated way to half do it.
Thanks Miguel that works a treat.

"Héctor Miguel" wrote:

hi, Rob !

I'd like to return a yes or no response.
at the moment only the YES is working.
Can anyone give me a tip?
Thanks
Rob

=IF(A6=VLOOKUP(A6,'[Catalogue Robs Test Stock
Range.xls]Catalogue'!$1:$5000,1,FALSE),"YES","NO")

AFAIK, vlookup will try to find your lookup_value in the first column of
your lookup_table
so, it doesn't make sense to incude whole columns by reference of the
entire rows -?-

try with:

=if(countif('[Catalogue Robs Test Stock
Range.xls]Catalogue'!$A$1:$A$5000,A6),"YES,"NO")

hth,
hector.

Wouldn't you need the full path if the file is closed?

Pete

On Sep 17, 7:06*pm, "T. Valko" wrote:
Note that COUNTIF requires the source file be open. An alternative that
works whether the source file is open or closed:

=IF(COUNT(MATCH(A6,'[Catalogue Robs Test Stock
Range.xls]Catalogue'!$A$1:$A$5000,0)),"YES,"NO")

--
Biff
Microsoft Excel MVP

hi, all !

Pete_UK wrote in message ...
Wouldn't you need the full path if the file is closed?

since OP posted a formula without the full-path, I assumed he's working (only ?) with source file opened
as soon as the source file is closed, each reference in formulae change to it's full-path (but)...
as Tony says, sumif(... countif(... doesn't work on closed files, so forth his proposal was...

regards,
hector.

T. Valko wrote in message ...
Note that COUNTIF requires the source file be open.
An alternative that works whether the source file is open or closed:
=IF(COUNT(MATCH(A6,'[Catalogue Robs Test Stock Range.xls]Catalogue'!$A$1:$A$5000,0)),"YES,"NO")