Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
I need an Equation in a cell to search a workbook not worksheet
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
I need an Equation in a cell to search a workbook not worksheet
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
I need an Equation in a cell to search a workbook not workshee
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? "Héctor Miguel" wrote: 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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
I need an Equation in a cell to search a workbook not workshee
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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
I need an Equation in a cell to search a workbook not workshee
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. "Héctor Miguel" wrote: 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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
I need an Equation in a cell to search a workbook not workshee
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do I refer a cell to workbook name not worksheet | Excel Discussion (Misc queries) | |||
How do I take a cell in one worksheet and add it into the formula in a different worksheet in the same workbook? | Excel Worksheet Functions | |||
Search Data from one Workbook and copy it into another Workbook | Excel Discussion (Misc queries) | |||
need help with an equation in worksheet to populate numbers onto c | Excel Worksheet Functions | |||
How can I link cell colours from worksheet to worksheet/workbook? | Excel Worksheet Functions |