I need an Equation in a cell to search a workbook not worksheet

[MWhaley]

Here is my problem.

I've got two workbooks, one has over 300 worksheets in it and one only has
two worksheets. In the workbook that has two worksheets I've got two columns
A1 has a bar codes in in from A1 through A3100. Column two is empty. I'm
trying to uses an equation in B1 that will do the following but in and
equation form. Look into Workbook one and if any cell has the barcode that is
in A1 than state a Yes if not state a No. I can do it for a worksheet but I'm
trying it for the entire workbook. This will cut having to do it over 300
different times.

 

[Héctor Miguel]

hi, !

you could have/build an horizontal array for your ~300 worksheet names (in the first workbook)
and then use a formula like the following (note that first workbook shall be open):

[B1] =if(sumproduct(countif(indirect("'first workbook.xls"&{"array";"of";"sheet";"names"}&"'!a:a"),a1)),"yes","no")

hth,
hector.

__ OP __

 

[MWhaley]

Thank you so much but I've only got one question, I'm good in Excel but I've
never dealt with Array's. Could you point me in the right direction to build
this array that your talking about below?

hi, !

you could have/build an horizontal array for your ~300 worksheet names (in the first workbook)
and then use a formula like the following (note that first workbook shall be open):

[B1] =if(sumproduct(countif(indirect("'first workbook.xls"&{"array";"of";"sheet";"names"}&"'!a:a"),a1)),"yes","no")

hth,
hector.

__ OP __

I've got two workbooks, one has over 300 worksheets in it and one only has two worksheets.
In the workbook that has two worksheets I've got two columns A1 has a bar codes in in from A1 through A3100.
Column two is empty. I'm trying to uses an equation in B1 that will do the following but in and equation form.
Look into Workbook one and if any cell has the barcode that is in A1 than state a Yes if not state a No.
I can do it for a worksheet but I'm trying it for the entire workbook. This will cut having to do it over 300 different times.

 

[Héctor Miguel]

hi, !

Thank you so much but I've only got one question, I'm good in Excel but I've never dealt with Array's.
Could you point me in the right direction to build this array that your talking about below?

let's say you put the name of your other workbook (i.e.)
[A1] -> "first workbook.xls" (no quotes) and the ~300 sheet names en [A2:A301]
with the bar-code to seek in [B1]

[C1] =if(sumproduct(countif(indirect("'["&a1&"]"&a2:a301&"'!a:a"),b1)),"yes","no")

*IF* all of your ~300 sheet names shares a pattern (shet1, sheet2...sheet300)
you could by-pass the a2:a301 range and build an array (but I don't know the sheets names)

hth,
hector.

__ previous __

you could have/build an horizontal array for your ~300 worksheet names (in the first workbook)
and then use a formula like the following (note that first workbook shall be open):

[B1] =if(sumproduct(countif(indirect("'first workbook.xls"&{"array";"of";"sheet";"names"}&"'!a:a"),a1)),"yes","no")

__ OP __

I've got two workbooks, one has over 300 worksheets in it and one only has two worksheets.
In the workbook that has two worksheets I've got two columns A1 has a bar codes in in from A1 through A3100.
Column two is empty. I'm trying to uses an equation in B1 that will do the following but in and equation form.
Look into Workbook one and if any cell has the barcode that is in A1 than state a Yes if not state a No.
I can do it for a worksheet but I'm trying it for the entire workbook. This will cut having to do it over 300 different times.

 

[MWhaley]

Thanks, but I'm still having some troubles with this. The name of my first
workbook is, 3100. The name of my second work book is RA Recreates 2008. The
second workbook is the one with over 300 worksheets in it. Each worksheet is
a date, ex( 1-2, 1-3, 1-4 and so on). In the First workbook named 3100 I only
have one worksheet with one column of information. In the column it has
Barcode number not the barcode font, ex( Reeejkjkjkeio) it's 12 digits. What
I'm trying to do is in Column 2 of the worksheet I'm wanting to look through
all the worksheets of the other workbook to find if the barcode in A1 is in
it. If so return a 'Yes' if not return a 'No'. I hope the more details can
get us to the solution.

hi, !

Thank you so much but I've only got one question, I'm good in Excel but I've never dealt with Array's.
Could you point me in the right direction to build this array that your talking about below?

let's say you put the name of your other workbook (i.e.)
[A1] -> "first workbook.xls" (no quotes) and the ~300 sheet names en [A2:A301]
with the bar-code to seek in [B1]

[C1] =if(sumproduct(countif(indirect("'["&a1&"]"&a2:a301&"'!a:a"),b1)),"yes","no")

*IF* all of your ~300 sheet names shares a pattern (shet1, sheet2...sheet300)
you could by-pass the a2:a301 range and build an array (but I don't know the sheets names)

hth,
hector.

__ previous __

you could have/build an horizontal array for your ~300 worksheet names (in the first workbook)
and then use a formula like the following (note that first workbook shall be open):

[B1] =if(sumproduct(countif(indirect("'first workbook.xls"&{"array";"of";"sheet";"names"}&"'!a:a"),a1)),"yes","no")

__ OP __
I've got two workbooks, one has over 300 worksheets in it and one only has two worksheets.
In the workbook that has two worksheets I've got two columns A1 has a bar codes in in from A1 through A3100.
Column two is empty. I'm trying to uses an equation in B1 that will do the following but in and equation form.
Look into Workbook one and if any cell has the barcode that is in A1 than state a Yes if not state a No.
I can do it for a worksheet but I'm trying it for the entire workbook. This will cut having to do it over 300 different times.

 

[Héctor Miguel]

hi, !

ok, let me (try to) explain (AFAIK) how it should/must work...

1) I createed a workbook (named: RA Recreates 2008) with the following FIVE sheets-date names:
1-2, 1-3, 1-4, 1-6, 1-7 (please, note that worksheet-date name "1-5" IS MISSING in the serie, it is important)

2) I'm sure that in some of this 5 sheet-date there is a code: Reeejkjkjkeio (barcode number not the barcode font)

3) in a brand new workbook, in sheet1!a1 I put/look/search for this code: Reeejkjkjkeio

4) first formula attempt:
=if(sumproduct(countif(indirect("'[ra recreates 2008]"&{"1-2";"1-3";"1-4";"1-5";"1-6";"1-7"}&"'!a:a"),a1)),"yes","no")
result: N/A error value
cause: the "missing" date-sheet name of "1-5" (remember item 1) above)

5) second formula attempt (obviously, I droped-out the missing sheet from the array)
=if(sumproduct(countif(indirect("'[ra recreates 2008]"&{"1-2";"1-3";"1-4";"1-6";"1-7"}&"'!a:a"),a1)),"yes","no")
result: YES

6) *IF* there is NO need to by-pass "certain" date-sheet names -???-
the first array (first attempt): -> indirect("'[ra recreates 2008]"&{"1-2";"1-3";"1-4";"1-5";"1-6";"1-7"}&"'!a:a")
could it be shortened like : -> indirect("'[ra recreates 2008]1-"&row(indirect("2:7"))&"'!a:a")
so you can expand date-sheet names within row(indirect("2:7")) far and beyond

the constraint: the date-sheet names as a result from the array *must* exist in the source workbook
that's why I told you:
"> *IF* all of your ~300 sheet names shares a pattern (shet1, sheet2...sheet300)"
"> you could by-pass the a2:a301 range and build an array (but I don't know the sheets names)"

if any doubt (or further information...) would you please comment ?
hth,
hector.

__ OP __