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 __
... 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.