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 ?