| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
reference to an external file
If I want to copy one cell from another file I will do
=[FileName.xls]SheetName!$I$10 But if I want the file to vary depending on a cell A1 value... I mean If cell A1 value is equal to Q1 then file name will be [Q1 FileName.xls] If cell A1 value is equal to Q2 then file name will be [Q2 FileName.xls] How do I make a formula so the file name depend on the cell value I tried [A1&" FileName.xls"]SheetName!$I$10 but did not work.... How can I do it? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
reference to an external file
USE INDIRECT()
=INDIRECT("'[" & A1 & "FileName.xls]Sheetname'!$I$10") 'Try the below to get more familiar In Book1, Sheet1, cell A1 type: "This is a test" (without the quotation marks). In Book2, Sheet1, cell A1 type: "Book1" (without the quotation marks). In Book2, Sheet1, cell A2 type: "Sheet1" (without the quotation marks). In Book2, Sheet1, cell A3 type: "A1" (without the quotation marks). Save both workbooks. In Book2, Sheet1, cell B1 type the following formula: =INDIRECT("'["&A1&".xls]"&A2&"'!"&A3) A1 = Workbook.xls B1 = Sheet1 C1 = Hello =INDIRECT("[" &A1 & "]" & B1 &"!" & "C1") If this post helps click Yes --------------- Jacob Skaria "Alberto Ast" wrote: If I want to copy one cell from another file I will do =[FileName.xls]SheetName!$I$10 But if I want the file to vary depending on a cell A1 value... I mean If cell A1 value is equal to Q1 then file name will be [Q1 FileName.xls] If cell A1 value is equal to Q2 then file name will be [Q2 FileName.xls] How do I make a formula so the file name depend on the cell value I tried [A1&" FileName.xls"]SheetName!$I$10 but did not work.... How can I do it? |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
reference to an external file
It looks like it should work but my application is a little more complex
because I have SUMPRODUCT involved.... my actual formula on my cell is as below =IF(SUMPRODUCT(('[FM Serial Label Print Log - R1.3.xls]Log'!$J$6:$J$15=F7)*('[FM Serial Label Print Log - R1.3.xls]Log'!$K$6:$K$15<=G7)*('[FM Serial Label Print Log - R1.3.xls]Log'!$L$6:$L$15=G7))0=FALSE,"Invalid S/N",IF(E71,"Duplicate","Yes")) Then I need the "FM" on the file name to depent in a cell value. Can you help me out? "Jacob Skaria" wrote: USE INDIRECT() =INDIRECT("'[" & A1 & "FileName.xls]Sheetname'!$I$10") 'Try the below to get more familiar In Book1, Sheet1, cell A1 type: "This is a test" (without the quotation marks). In Book2, Sheet1, cell A1 type: "Book1" (without the quotation marks). In Book2, Sheet1, cell A2 type: "Sheet1" (without the quotation marks). In Book2, Sheet1, cell A3 type: "A1" (without the quotation marks). Save both workbooks. In Book2, Sheet1, cell B1 type the following formula: =INDIRECT("'["&A1&".xls]"&A2&"'!"&A3) A1 = Workbook.xls B1 = Sheet1 C1 = Hello =INDIRECT("[" &A1 & "]" & B1 &"!" & "C1") If this post helps click Yes --------------- Jacob Skaria "Alberto Ast" wrote: If I want to copy one cell from another file I will do =[FileName.xls]SheetName!$I$10 But if I want the file to vary depending on a cell A1 value... I mean If cell A1 value is equal to Q1 then file name will be [Q1 FileName.xls] If cell A1 value is equal to Q2 then file name will be [Q2 FileName.xls] How do I make a formula so the file name depend on the cell value I tried [A1&" FileName.xls"]SheetName!$I$10 but did not work.... How can I do it? |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
reference to an external file
Check out the below link where you can download an add-in called Morefunc
which has a function called INDIRECT.EXT http://xcell05.free.fr/morefunc/english/index.htm If this post helps click Yes --------------- Jacob Skaria "Alberto Ast" wrote: It looks like it should work but my application is a little more complex because I have SUMPRODUCT involved.... my actual formula on my cell is as below =IF(SUMPRODUCT(('[FM Serial Label Print Log - R1.3.xls]Log'!$J$6:$J$15=F7)*('[FM Serial Label Print Log - R1.3.xls]Log'!$K$6:$K$15<=G7)*('[FM Serial Label Print Log - R1.3.xls]Log'!$L$6:$L$15=G7))0=FALSE,"Invalid S/N",IF(E71,"Duplicate","Yes")) Then I need the "FM" on the file name to depent in a cell value. Can you help me out? "Jacob Skaria" wrote: USE INDIRECT() =INDIRECT("'[" & A1 & "FileName.xls]Sheetname'!$I$10") 'Try the below to get more familiar In Book1, Sheet1, cell A1 type: "This is a test" (without the quotation marks). In Book2, Sheet1, cell A1 type: "Book1" (without the quotation marks). In Book2, Sheet1, cell A2 type: "Sheet1" (without the quotation marks). In Book2, Sheet1, cell A3 type: "A1" (without the quotation marks). Save both workbooks. In Book2, Sheet1, cell B1 type the following formula: =INDIRECT("'["&A1&".xls]"&A2&"'!"&A3) A1 = Workbook.xls B1 = Sheet1 C1 = Hello =INDIRECT("[" &A1 & "]" & B1 &"!" & "C1") If this post helps click Yes --------------- Jacob Skaria "Alberto Ast" wrote: If I want to copy one cell from another file I will do =[FileName.xls]SheetName!$I$10 But if I want the file to vary depending on a cell A1 value... I mean If cell A1 value is equal to Q1 then file name will be [Q1 FileName.xls] If cell A1 value is equal to Q2 then file name will be [Q2 FileName.xls] How do I make a formula so the file name depend on the cell value I tried [A1&" FileName.xls"]SheetName!$I$10 but did not work.... How can I do it? |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
reference to an external file
First, if you're going to use =indirect(), then the sending file must be open.
You'll see an error if that sending workbook is not open. =IF(SUMPRODUCT((indirect('[" & x9999 & " Serial Label Print Log - R1.3.xls]Log'!J6:J15")=F7) * ..... If your file will be closed, then Laurent Longre has an addin (morefunc.xll) at: http://xcell05.free.fr/ or http://xcell05.free.fr/morefunc/english/index.htm That includes =indirect.ext() that may help you. === If you have trouble getting to the site, then search google for indirect.ext. I found this alternative site: http://download.cnet.com/Morefunc/30...-10423159.html I didn't look to see if it was the most current version. I'd check the original site every so often to see if it's working. Alberto Ast wrote: It looks like it should work but my application is a little more complex because I have SUMPRODUCT involved.... my actual formula on my cell is as below =IF(SUMPRODUCT(('[FM Serial Label Print Log - R1.3.xls]Log'!$J$6:$J$15=F7)*('[FM Serial Label Print Log - R1.3.xls]Log'!$K$6:$K$15<=G7)*('[FM Serial Label Print Log - R1.3.xls]Log'!$L$6:$L$15=G7))0=FALSE,"Invalid S/N",IF(E71,"Duplicate","Yes")) Then I need the "FM" on the file name to depent in a cell value. Can you help me out? "Jacob Skaria" wrote: USE INDIRECT() =INDIRECT("'[" & A1 & "FileName.xls]Sheetname'!$I$10") 'Try the below to get more familiar In Book1, Sheet1, cell A1 type: "This is a test" (without the quotation marks). In Book2, Sheet1, cell A1 type: "Book1" (without the quotation marks). In Book2, Sheet1, cell A2 type: "Sheet1" (without the quotation marks). In Book2, Sheet1, cell A3 type: "A1" (without the quotation marks). Save both workbooks. In Book2, Sheet1, cell B1 type the following formula: =INDIRECT("'["&A1&".xls]"&A2&"'!"&A3) A1 = Workbook.xls B1 = Sheet1 C1 = Hello =INDIRECT("[" &A1 & "]" & B1 &"!" & "C1") If this post helps click Yes --------------- Jacob Skaria "Alberto Ast" wrote: If I want to copy one cell from another file I will do =[FileName.xls]SheetName!$I$10 But if I want the file to vary depending on a cell A1 value... I mean If cell A1 value is equal to Q1 then file name will be [Q1 FileName.xls] If cell A1 value is equal to Q2 then file name will be [Q2 FileName.xls] How do I make a formula so the file name depend on the cell value I tried [A1&" FileName.xls"]SheetName!$I$10 but did not work.... How can I do it? -- Dave Peterson |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
reference to an external file
Thanks.. it has very good infomation.. for now I will stay with your previous
tips. "Jacob Skaria" wrote: Check out the below link where you can download an add-in called Morefunc which has a function called INDIRECT.EXT http://xcell05.free.fr/morefunc/english/index.htm If this post helps click Yes --------------- Jacob Skaria "Alberto Ast" wrote: It looks like it should work but my application is a little more complex because I have SUMPRODUCT involved.... my actual formula on my cell is as below =IF(SUMPRODUCT(('[FM Serial Label Print Log - R1.3.xls]Log'!$J$6:$J$15=F7)*('[FM Serial Label Print Log - R1.3.xls]Log'!$K$6:$K$15<=G7)*('[FM Serial Label Print Log - R1.3.xls]Log'!$L$6:$L$15=G7))0=FALSE,"Invalid S/N",IF(E71,"Duplicate","Yes")) Then I need the "FM" on the file name to depent in a cell value. Can you help me out? "Jacob Skaria" wrote: USE INDIRECT() =INDIRECT("'[" & A1 & "FileName.xls]Sheetname'!$I$10") 'Try the below to get more familiar In Book1, Sheet1, cell A1 type: "This is a test" (without the quotation marks). In Book2, Sheet1, cell A1 type: "Book1" (without the quotation marks). In Book2, Sheet1, cell A2 type: "Sheet1" (without the quotation marks). In Book2, Sheet1, cell A3 type: "A1" (without the quotation marks). Save both workbooks. In Book2, Sheet1, cell B1 type the following formula: =INDIRECT("'["&A1&".xls]"&A2&"'!"&A3) A1 = Workbook.xls B1 = Sheet1 C1 = Hello =INDIRECT("[" &A1 & "]" & B1 &"!" & "C1") If this post helps click Yes --------------- Jacob Skaria "Alberto Ast" wrote: If I want to copy one cell from another file I will do =[FileName.xls]SheetName!$I$10 But if I want the file to vary depending on a cell A1 value... I mean If cell A1 value is equal to Q1 then file name will be [Q1 FileName.xls] If cell A1 value is equal to Q2 then file name will be [Q2 FileName.xls] How do I make a formula so the file name depend on the cell value I tried [A1&" FileName.xls"]SheetName!$I$10 but did not work.... How can I do it? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Concatenate path and file string for external reference | Excel Worksheet Functions | |||
| how to call a reference to a cell in external file | Excel Worksheet Functions | |||
| external reference to file in the same folder | Excel Discussion (Misc queries) | |||
| How do I reference external data from a file, file name found in . | Excel Discussion (Misc queries) | |||
| External Reference | Excel Discussion (Misc queries) | |||
![Alberto Ast[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
