Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup in External Worksheet
I have two worksheets - one with a database and one that does a lookup on the
database. How can I pull information from the database based on the information entered in the lookup worksheet? My Lookup worksheet contains two fields that need data entry by the end user. B2 User enters Jcode B3 User enters drug name B4 Populates information based on what's found in the database My current formula in the lookup file is: (INDEX('2010 HBR Query Tool Only.xlsx'!DrugName, MATCH(B2,'2010 HBR Query Tool Only.xlsx'!JCode,0),0). The formula above is currently using the database that's contained in the same workbook as the lookup. If I want to use another file, I assume that I would just change the name of the file to the name of the external file. However, does Excel require the external file to be open in order to perform its operation? The contents of the database are used for different workbooks and different functions as well as different users in different depts with different access. I want to have one version of the database so I won't have to update more than one file with changes. If the file has to be open in order for the formulas to work, is there a way that I can code the lookup document so that it will open the database automatically (behind the scenes for the most people) and then close when the lookup file is closed? I don't know if this is even possible or not - so if anyone can help me with this I would appreciate it. i.e. (I posted this question earlier - but the posting didn't stay in the forum? So, if you find this is a duplicate, I apologize.) -- Lady |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup in External Worksheet
Lady Success wrote:
I have two worksheets - one with a database and one that does a lookup on the database. How can I pull information from the database based on the information entered in the lookup worksheet? My Lookup worksheet contains two fields that need data entry by the end user. B2 User enters Jcode B3 User enters drug name B4 Populates information based on what's found in the database My current formula in the lookup file is: (INDEX('2010 HBR Query Tool Only.xlsx'!DrugName, MATCH(B2,'2010 HBR Query Tool Only.xlsx'!JCode,0),0). The formula above is currently using the database that's contained in the same workbook as the lookup. If I want to use another file, I assume that I would just change the name of the file to the name of the external file. However, does Excel require the external file to be open in order to perform its operation? The contents of the database are used for different workbooks and different functions as well as different users in different depts with different access. I want to have one version of the database so I won't have to update more than one file with changes. If the file has to be open in order for the formulas to work, is there a way that I can code the lookup document so that it will open the database automatically (behind the scenes for the most people) and then close when the lookup file is closed? I don't know if this is even possible or not - so if anyone can help me with this I would appreciate it. i.e. (I posted this question earlier - but the posting didn't stay in the forum? So, if you find this is a duplicate, I apologize.) Yes, it's possible, and no, the other workbook does not have to be open for this to work. However, when setting up the formulas it is easier to get the syntax correct with the other workbook open. Assuming you have the external book open, basically all you have to do is start entering your formula and when you need to refer to the other workbook simply switch windows, point to the range you need (or perhaps call up the named range picker in your case) in the other workbook, switch back, and continue. Once this is done, the external book need not be opened again. When someone opens the lookup file they will probably be prompted to refresh the external link values, which is the normal thing to do. Excel will call up the needed values and refresh the lookup file without actually opening the other book. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup in External Worksheet
Thank you for the information.... However, it doesn't appear that I can
point and click over to my external worksheet. I thought initially that when I was trying to do it that it was because I was accessing through the wizard. But when I just tried it on a blank worksheet, it didn't allow me to do it. It's like each worksheet is working independently of each other. Am I doing something wrong - or is it a setting somewhere in Excel? I could have sworn that I've been able to access cells from other workbooks in the past. -- Lady "smartin" wrote: Lady Success wrote: I have two worksheets - one with a database and one that does a lookup on the database. How can I pull information from the database based on the information entered in the lookup worksheet? My Lookup worksheet contains two fields that need data entry by the end user. B2 User enters Jcode B3 User enters drug name B4 Populates information based on what's found in the database My current formula in the lookup file is: (INDEX('2010 HBR Query Tool Only.xlsx'!DrugName, MATCH(B2,'2010 HBR Query Tool Only.xlsx'!JCode,0),0). The formula above is currently using the database that's contained in the same workbook as the lookup. If I want to use another file, I assume that I would just change the name of the file to the name of the external file. However, does Excel require the external file to be open in order to perform its operation? The contents of the database are used for different workbooks and different functions as well as different users in different depts with different access. I want to have one version of the database so I won't have to update more than one file with changes. If the file has to be open in order for the formulas to work, is there a way that I can code the lookup document so that it will open the database automatically (behind the scenes for the most people) and then close when the lookup file is closed? I don't know if this is even possible or not - so if anyone can help me with this I would appreciate it. i.e. (I posted this question earlier - but the posting didn't stay in the forum? So, if you find this is a duplicate, I apologize.) Yes, it's possible, and no, the other workbook does not have to be open for this to work. However, when setting up the formulas it is easier to get the syntax correct with the other workbook open. Assuming you have the external book open, basically all you have to do is start entering your formula and when you need to refer to the other workbook simply switch windows, point to the range you need (or perhaps call up the named range picker in your case) in the other workbook, switch back, and continue. Once this is done, the external book need not be opened again. When someone opens the lookup file they will probably be prompted to refresh the external link values, which is the normal thing to do. Excel will call up the needed values and refresh the lookup file without actually opening the other book. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup in External Worksheet
Ah yes, this does not work if you open the workbooks in separate
instances of Excel. You have to open both workbooks in the same instance. I forgot about that caveat. Lady Success wrote: Thank you for the information.... However, it doesn't appear that I can point and click over to my external worksheet. I thought initially that when I was trying to do it that it was because I was accessing through the wizard. But when I just tried it on a blank worksheet, it didn't allow me to do it. It's like each worksheet is working independently of each other. Am I doing something wrong - or is it a setting somewhere in Excel? I could have sworn that I've been able to access cells from other workbooks in the past. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup in External Worksheet
I was able to point and click to the external worksheet after following your
instructions. But - when I input my data into the entry worksheet, it gives me a #Value! error. When I researched this earlier, the research indicated that might happen if the documents were not both saved as .xlsx. I double checked and both files are saved as .xlsx files. The formulas work fine if the database is stored within the same worksheet as the input data sheet. Any ideas? Also, will this work with array formulas? My formula looks like: =IF('2010 HBR Query Tool Only.xlsx'!DRUGENTRY="",0,COUNTIF('F:\Query Drug List-2.xlsx'!DrugList,"*"&'2010 HBR Query Tool Only.xlsx'!DRUGENTRY&"*")) Any assistance would be greatly appreciated! -- Lady "smartin" wrote: Ah yes, this does not work if you open the workbooks in separate instances of Excel. You have to open both workbooks in the same instance. I forgot about that caveat. Lady Success wrote: Thank you for the information.... However, it doesn't appear that I can point and click over to my external worksheet. I thought initially that when I was trying to do it that it was because I was accessing through the wizard. But when I just tried it on a blank worksheet, it didn't allow me to do it. It's like each worksheet is working independently of each other. Am I doing something wrong - or is it a setting somewhere in Excel? I could have sworn that I've been able to access cells from other workbooks in the past. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup in External Worksheet
I have a similair issue and had a question. I am not using INDEX/MATCH but
SUMIF instead. Will this work with this expression as well? I have a spreadsheet using SUMIFs to summarize data from 6 separate workbooks and every time I open the main document I have to open each workbook to make the connection. Even if I go through the update links portion I can't update any information without opening all 6 files. If it doesn't work with SUMIF I could create a sum worksheet on each of the 6 and use INDEX/Match to get the data. Thanks! "smartin" wrote: Lady Success wrote: I have two worksheets - one with a database and one that does a lookup on the database. How can I pull information from the database based on the information entered in the lookup worksheet? My Lookup worksheet contains two fields that need data entry by the end user. B2 User enters Jcode B3 User enters drug name B4 Populates information based on what's found in the database My current formula in the lookup file is: (INDEX('2010 HBR Query Tool Only.xlsx'!DrugName, MATCH(B2,'2010 HBR Query Tool Only.xlsx'!JCode,0),0). The formula above is currently using the database that's contained in the same workbook as the lookup. If I want to use another file, I assume that I would just change the name of the file to the name of the external file. However, does Excel require the external file to be open in order to perform its operation? The contents of the database are used for different workbooks and different functions as well as different users in different depts with different access. I want to have one version of the database so I won't have to update more than one file with changes. If the file has to be open in order for the formulas to work, is there a way that I can code the lookup document so that it will open the database automatically (behind the scenes for the most people) and then close when the lookup file is closed? I don't know if this is even possible or not - so if anyone can help me with this I would appreciate it. i.e. (I posted this question earlier - but the posting didn't stay in the forum? So, if you find this is a duplicate, I apologize.) Yes, it's possible, and no, the other workbook does not have to be open for this to work. However, when setting up the formulas it is easier to get the syntax correct with the other workbook open. Assuming you have the external book open, basically all you have to do is start entering your formula and when you need to refer to the other workbook simply switch windows, point to the range you need (or perhaps call up the named range picker in your case) in the other workbook, switch back, and continue. Once this is done, the external book need not be opened again. When someone opens the lookup file they will probably be prompted to refresh the external link values, which is the normal thing to do. Excel will call up the needed values and refresh the lookup file without actually opening the other book. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup in External Worksheet
Saved from a previous post:
There are functions that don't work when the "sending" workbook is closed. =sumif(), =countif() and =indirect() are a few. But there are workarounds: =sumproduct(--('[yourfilename]Fargo'!$B$10:$B$134="April-09")) If those cells were really dates: =sumproduct(--(text('[yourfilename]Fargo'!$B$10:$B$134,"yyyymm")="200904")) Build the formula with your workbooks open and let excel worry about the syntax when you close the sending workbook. Adjust the ranges to match--but you can't use whole columns (except in xl2007). =sumproduct() likes to work with numbers. The -- stuff changes trues and falses to 1's and 0's. Bob Phillips explains =sumproduct() in much more detail he http://www.xldynamic.com/source/xld.SUMPRODUCT.html And J.E. McGimpsey has some notes at: http://mcgimpsey.com/excel/formulae/doubleneg.html JICDB wrote: I have a similair issue and had a question. I am not using INDEX/MATCH but SUMIF instead. Will this work with this expression as well? I have a spreadsheet using SUMIFs to summarize data from 6 separate workbooks and every time I open the main document I have to open each workbook to make the connection. Even if I go through the update links portion I can't update any information without opening all 6 files. If it doesn't work with SUMIF I could create a sum worksheet on each of the 6 and use INDEX/Match to get the data. Thanks! "smartin" wrote: Lady Success wrote: I have two worksheets - one with a database and one that does a lookup on the database. How can I pull information from the database based on the information entered in the lookup worksheet? My Lookup worksheet contains two fields that need data entry by the end user. B2 User enters Jcode B3 User enters drug name B4 Populates information based on what's found in the database My current formula in the lookup file is: (INDEX('2010 HBR Query Tool Only.xlsx'!DrugName, MATCH(B2,'2010 HBR Query Tool Only.xlsx'!JCode,0),0). The formula above is currently using the database that's contained in the same workbook as the lookup. If I want to use another file, I assume that I would just change the name of the file to the name of the external file. However, does Excel require the external file to be open in order to perform its operation? The contents of the database are used for different workbooks and different functions as well as different users in different depts with different access. I want to have one version of the database so I won't have to update more than one file with changes. If the file has to be open in order for the formulas to work, is there a way that I can code the lookup document so that it will open the database automatically (behind the scenes for the most people) and then close when the lookup file is closed? I don't know if this is even possible or not - so if anyone can help me with this I would appreciate it. i.e. (I posted this question earlier - but the posting didn't stay in the forum? So, if you find this is a duplicate, I apologize.) Yes, it's possible, and no, the other workbook does not have to be open for this to work. However, when setting up the formulas it is easier to get the syntax correct with the other workbook open. Assuming you have the external book open, basically all you have to do is start entering your formula and when you need to refer to the other workbook simply switch windows, point to the range you need (or perhaps call up the named range picker in your case) in the other workbook, switch back, and continue. Once this is done, the external book need not be opened again. When someone opens the lookup file they will probably be prompted to refresh the external link values, which is the normal thing to do. Excel will call up the needed values and refresh the lookup file without actually opening the other book. -- Dave Peterson |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup in External Worksheet
Dave - thank you so much for your help. This worked perfect. I really liked
the links as well. They are great explanations as to why this works. It is also really cool that this info is not in the Microsoft Help because the formula wasn't created for this usage but the great minds of Excel users thought outside the box (again)!!! I really appreciate your help. "Dave Peterson" wrote: Saved from a previous post: There are functions that don't work when the "sending" workbook is closed. =sumif(), =countif() and =indirect() are a few. But there are workarounds: =sumproduct(--('[yourfilename]Fargo'!$B$10:$B$134="April-09")) If those cells were really dates: =sumproduct(--(text('[yourfilename]Fargo'!$B$10:$B$134,"yyyymm")="200904")) Build the formula with your workbooks open and let excel worry about the syntax when you close the sending workbook. Adjust the ranges to match--but you can't use whole columns (except in xl2007). =sumproduct() likes to work with numbers. The -- stuff changes trues and falses to 1's and 0's. Bob Phillips explains =sumproduct() in much more detail he http://www.xldynamic.com/source/xld.SUMPRODUCT.html And J.E. McGimpsey has some notes at: http://mcgimpsey.com/excel/formulae/doubleneg.html JICDB wrote: I have a similair issue and had a question. I am not using INDEX/MATCH but SUMIF instead. Will this work with this expression as well? I have a spreadsheet using SUMIFs to summarize data from 6 separate workbooks and every time I open the main document I have to open each workbook to make the connection. Even if I go through the update links portion I can't update any information without opening all 6 files. If it doesn't work with SUMIF I could create a sum worksheet on each of the 6 and use INDEX/Match to get the data. Thanks! "smartin" wrote: Lady Success wrote: I have two worksheets - one with a database and one that does a lookup on the database. How can I pull information from the database based on the information entered in the lookup worksheet? My Lookup worksheet contains two fields that need data entry by the end user. B2 User enters Jcode B3 User enters drug name B4 Populates information based on what's found in the database My current formula in the lookup file is: (INDEX('2010 HBR Query Tool Only.xlsx'!DrugName, MATCH(B2,'2010 HBR Query Tool Only.xlsx'!JCode,0),0). The formula above is currently using the database that's contained in the same workbook as the lookup. If I want to use another file, I assume that I would just change the name of the file to the name of the external file. However, does Excel require the external file to be open in order to perform its operation? The contents of the database are used for different workbooks and different functions as well as different users in different depts with different access. I want to have one version of the database so I won't have to update more than one file with changes. If the file has to be open in order for the formulas to work, is there a way that I can code the lookup document so that it will open the database automatically (behind the scenes for the most people) and then close when the lookup file is closed? I don't know if this is even possible or not - so if anyone can help me with this I would appreciate it. i.e. (I posted this question earlier - but the posting didn't stay in the forum? So, if you find this is a duplicate, I apologize.) Yes, it's possible, and no, the other workbook does not have to be open for this to work. However, when setting up the formulas it is easier to get the syntax correct with the other workbook open. Assuming you have the external book open, basically all you have to do is start entering your formula and when you need to refer to the other workbook simply switch windows, point to the range you need (or perhaps call up the named range picker in your case) in the other workbook, switch back, and continue. Once this is done, the external book need not be opened again. When someone opens the lookup file they will probably be prompted to refresh the external link values, which is the normal thing to do. Excel will call up the needed values and refresh the lookup file without actually opening the other book. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to link to external worksheet | New Users to Excel | |||
Lookup Value in an External Spreadsheet | Excel Discussion (Misc queries) | |||
Reference external worksheet whose name is in a cell | Excel Worksheet Functions | |||
How can I link from an external website to a particular worksheet | Excel Worksheet Functions | |||
Lookup in data from external datasource | Excel Worksheet Functions |