Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Referencing a different Workbook
I have a workbook that references different sheets in a different workbook.
But when I start a new project I must update all the references with the new filenames using "Replace". Is it possible to enter the new filename into a cell and just have all my references point to that cell instead of directly to the other workbook? Thanks for any help! Ken |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Referencing a different Workbook
You would have to use the INDIRECT function.
For Instance... A1: Book1 A2: =INDIRECT("["&A1&"]Sheet1!A1") A2 references cell A1 in Sheet1 of Book1. You could shorten it some by using... A1: [Book1]Sheet1! A2: =INDIRECT(A1&"A1") "Ken" wrote: I have a workbook that references different sheets in a different workbook. But when I start a new project I must update all the references with the new filenames using "Replace". Is it possible to enter the new filename into a cell and just have all my references point to that cell instead of directly to the other workbook? Thanks for any help! Ken |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Referencing a different Workbook
I have been trying to get the Indirect to work but I keep getting a REF
error. Here is what I tried. Book "2" has a value of 5 in A1. In Book 1 I have this. A1= C:\Documents and Settings\kf\Desktop\2.xls A2= =INDIRECT("["&A1&"]Sheet1!A1") AND I tried In Book 1 I have this. A1= =[C:\Documents and Settings\kf\Desktop\2.xls]Sheet1! A2= =INDIRECT(A1&"A1") "Sloth" wrote: You would have to use the INDIRECT function. For Instance... A1: Book1 A2: =INDIRECT("["&A1&"]Sheet1!A1") A2 references cell A1 in Sheet1 of Book1. You could shorten it some by using... A1: [Book1]Sheet1! A2: =INDIRECT(A1&"A1") "Ken" wrote: I have a workbook that references different sheets in a different workbook. But when I start a new project I must update all the references with the new filenames using "Replace". Is it possible to enter the new filename into a cell and just have all my references point to that cell instead of directly to the other workbook? Thanks for any help! Ken |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Referencing a different Workbook
"Ken" wrote:
... In Book 1 I have this. A1= =[C:\Documents and Settings\kf\Desktop\2.xls]Sheet1! A2= =INDIRECT(A1&"A1") Try these amendments: In A1: C:\Documents and Settings\kf\Desktop\[2.xls]Sheet1'! In A2: =INDIRECT("'"&A1&"A1") And, importantly, we need the linked book: 2.xls to be open as well. INDIRECT requires the linked book to be open for it to work, otherwise even with the correct syntax, etc, it'll show as #REF! -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Referencing a different Workbook
Sorry for the late reply, you have two problems.
1. Enclose the link in single quotes ' 2. The brackets go around the workbook name You want a result of something like this ='C:\Documents and Settings\kf\Desktop\[2.xls]Sheet1'!A1 Notice the ' goes in front and before the ! So we get to it with this. A1: C:\Documents and Settings\kf\Desktop\[2.xls] A2: =INDIRECT("'"&A1&"Sheet1'!A1") "Ken" wrote: I have been trying to get the Indirect to work but I keep getting a REF error. Here is what I tried. Book "2" has a value of 5 in A1. In Book 1 I have this. A1= C:\Documents and Settings\kf\Desktop\2.xls A2= =INDIRECT("["&A1&"]Sheet1!A1") AND I tried In Book 1 I have this. A1= =[C:\Documents and Settings\kf\Desktop\2.xls]Sheet1! A2= =INDIRECT(A1&"A1") "Sloth" wrote: You would have to use the INDIRECT function. For Instance... A1: Book1 A2: =INDIRECT("["&A1&"]Sheet1!A1") A2 references cell A1 in Sheet1 of Book1. You could shorten it some by using... A1: [Book1]Sheet1! A2: =INDIRECT(A1&"A1") "Ken" wrote: I have a workbook that references different sheets in a different workbook. But when I start a new project I must update all the references with the new filenames using "Replace". Is it possible to enter the new filename into a cell and just have all my references point to that cell instead of directly to the other workbook? Thanks for any help! Ken |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Referencing a different Workbook
Well, I have a bigger problem then. It is not possible to have the linked
workbook open. The link needs to access the file while it is closed. Is there another function that will get me the "link" results without having to open the other workbook first? Ken "Max" wrote: "Ken" wrote: ... In Book 1 I have this. A1= =[C:\Documents and Settings\kf\Desktop\2.xls]Sheet1! A2= =INDIRECT(A1&"A1") Try these amendments: In A1: C:\Documents and Settings\kf\Desktop\[2.xls]Sheet1'! In A2: =INDIRECT("'"&A1&"A1") And, importantly, we need the linked book: 2.xls to be open as well. INDIRECT requires the linked book to be open for it to work, otherwise even with the correct syntax, etc, it'll show as #REF! -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Referencing a different Workbook
Looks like you are out of look then, and will have to keep doing it the slow
way. Sorry to get your hopes up, but I wasn't aware that you need the file open, or that it was a problem. I believe you could use a macro that does the Find and Replace automatically, but I don't know a lot about VBA coding. "Ken" wrote: Well, I have a bigger problem then. It is not possible to have the linked workbook open. The link needs to access the file while it is closed. Is there another function that will get me the "link" results without having to open the other workbook first? Ken "Max" wrote: "Ken" wrote: ... In Book 1 I have this. A1= =[C:\Documents and Settings\kf\Desktop\2.xls]Sheet1! A2= =INDIRECT(A1&"A1") Try these amendments: In A1: C:\Documents and Settings\kf\Desktop\[2.xls]Sheet1'! In A2: =INDIRECT("'"&A1&"A1") And, importantly, we need the linked book: 2.xls to be open as well. INDIRECT requires the linked book to be open for it to work, otherwise even with the correct syntax, etc, it'll show as #REF! -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Referencing a different Workbook
Thanks for your help!
"Sloth" wrote: Looks like you are out of look then, and will have to keep doing it the slow way. Sorry to get your hopes up, but I wasn't aware that you need the file open, or that it was a problem. I believe you could use a macro that does the Find and Replace automatically, but I don't know a lot about VBA coding. "Ken" wrote: Well, I have a bigger problem then. It is not possible to have the linked workbook open. The link needs to access the file while it is closed. Is there another function that will get me the "link" results without having to open the other workbook first? Ken "Max" wrote: "Ken" wrote: ... In Book 1 I have this. A1= =[C:\Documents and Settings\kf\Desktop\2.xls]Sheet1! A2= =INDIRECT(A1&"A1") Try these amendments: In A1: C:\Documents and Settings\kf\Desktop\[2.xls]Sheet1'! In A2: =INDIRECT("'"&A1&"A1") And, importantly, we need the linked book: 2.xls to be open as well. INDIRECT requires the linked book to be open for it to work, otherwise even with the correct syntax, etc, it'll show as #REF! -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Referencing a different Workbook
As a last pitch here, you might want to browse this post by Harlan
where he provides the Function pull which works on closed workbooks: http://tinyurl.com/dkgc8 (Link intentionally points to the "Show original" version in google to avert problems in copy pasting the UDF) You can read the complete thread at: http://tinyurl.com/c6wpq As I don't have experience using Harlan's UDF as yet, start a new post if you need further help. -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
INDIRECT and Named Ranges referencing closed workbook | Excel Worksheet Functions | |||
Referencing another Workbook | Excel Discussion (Misc queries) | |||
How do I save an Excel workbook so that cells referencing another. | Excel Discussion (Misc queries) | |||
Unprotect Workbook | Excel Discussion (Misc queries) | |||
formula referencing another workbook | Excel Worksheet Functions |