I have a column of Text terms in column A in one work sheet and want to see
if the terms exist anywhere in a separate work book and show Y in D if it
does.

ie does term " 0df " in A1 exist in workbook X if yes then past Y in D1,
loop through to A27000 exist in wookbook X then past Y in D27,000.

any ideas ?

=IF(COUNTIF(A1,[X.xls]One!$A$1:$A$5000)0,Y in D,0)

I don't really understand the Y in D part, could you elaborate using names
of more than one letter where you are not referring to a column or give an
example of this from your data?

"chris 123456" wrote:

I have a column of Text terms in column A in one work sheet and want to see
if the terms exist anywhere in a separate work book and show Y in D if it
does.

ie does term " 0df " in A1 exist in workbook X if yes then past Y in D1,
loop through to A27000 exist in wookbook X then past Y in D27,000.

any ideas ?

Y just stands for Yes

Example

In work book 1 there is a term 01U in cell A1. I want to check to see if 01U
exists in any cell in workbook 2 which contains multiple sheets. If 01U is
found then place a Y indicating it was found in cell D1 of workbook 1.

repeat the query through all the entries in workbook 1 for all cells in
column A

Thanks for taking a look at this.





"~L" wrote:

=IF(COUNTIF(A1,[X.xls]One!$A$1:$A$5000)0,Y in D,0)

I don't really understand the Y in D part, could you elaborate using names
of more than one letter where you are not referring to a column or give an
example of this from your data?

"chris 123456" wrote:

I have a column of Text terms in column A in one work sheet and want to see
if the terms exist anywhere in a separate work book and show Y in D if it
does.

ie does term " 0df " in A1 exist in workbook X if yes then past Y in D1,
loop through to A27000 exist in wookbook X then past Y in D27,000.

any ideas ?

If you want the result of the formula in D1 to be be Y as a text string, you
need to enclose it in quotes, "Y".
--
David Biddulph

chris 123456 wrote:
Y just stands for Yes

Example

In work book 1 there is a term 01U in cell A1. I want to check to see
if 01U exists in any cell in workbook 2 which contains multiple
sheets. If 01U is found then place a Y indicating it was found in
cell D1 of workbook 1.

repeat the query through all the entries in workbook 1 for all cells
in column A

Thanks for taking a look at this.


"~L" wrote:

=IF(COUNTIF(A1,[X.xls]One!$A$1:$A$5000)0,Y in D,0)

I don't really understand the Y in D part, could you elaborate using
names of more than one letter where you are not referring to a
column or give an example of this from your data?

"chris 123456" wrote:

I have a column of Text terms in column A in one work sheet and
want to see if the terms exist anywhere in a separate work book and
show Y in D if it does.

ie does term " 0df " in A1 exist in workbook X if yes then past Y
in D1, loop through to A27000 exist in wookbook X then past Y in
D27,000.

any ideas ?

Unfortunately, only a few functions work with 3-D References (references that
span multiple worksheets) and none of them is well-suited to performing this
sort of check.

The addin Morefunc:
http://xcell05.free.fr/morefunc/english/

has Countif.3D which will solve this:

=IF(COUNTIF.3D([Workbook Name]'Name of first
sheet':NameOfSecondSheet!$A$1:$K$50,D1)0,"Y","")

"chris 123456" wrote:

Y just stands for Yes

Example

In work book 1 there is a term 01U in cell A1. I want to check to see if 01U
exists in any cell in workbook 2 which contains multiple sheets. If 01U is
found then place a Y indicating it was found in cell D1 of workbook 1.

repeat the query through all the entries in workbook 1 for all cells in
column A

Thanks for taking a look at this.





"~L" wrote:

=IF(COUNTIF(A1,[X.xls]One!$A$1:$A$5000)0,Y in D,0)

I don't really understand the Y in D part, could you elaborate using names
of more than one letter where you are not referring to a column or give an
example of this from your data?

"chris 123456" wrote:

I have a column of Text terms in column A in one work sheet and want to see
if the terms exist anywhere in a separate work book and show Y in D if it
does.

ie does term " 0df " in A1 exist in workbook X if yes then past Y in D1,
loop through to A27000 exist in wookbook X then past Y in D27,000.

any ideas ?