Have Geek Brain

Formula referencing other books

Given a list of book file names, write a formula to reference the specific cell value in the specified book.

A----------B-------C-----D--------------------------------E----------

workbook1  sheet1  A1    '[workbook1.xlsx]sheet1'!$A$1    =(????
workbook2  sheet1  A1    '[workbook2.xlsx]sheet1'!$A$1
workbook3  sheet1  A1    '[workbook3.xlsx]sheet1'!$A$1

Given A, B and C, I can create D, but haven't found a way to use it in a formula in E.

I am unable to reference the above line in D in the formula. Also INDIRECT doesn't work.

+1


a source to share


3 answers


According to Excel 2007 INDIRECT Help:

If ref_text refers to another book (xref), the other book should be open. If the original workbook is not open, INDIRECT returns #REF! error value.



Do the corresponding files have the option to open the parameter?

If not, maybe something like some scripts ...

+1


a source


I think you only need to get rid of the single quote characters in order to use INDIRECT. Works for me in Excel 2003 on Windows NT.

A13 ---- B13 ---- C13 - D13 ---------------------- E13 ---------- -



w1.xls Sheet1 $ A $ 1 = "[" & A13 & "]" & B13 & "!" & C13 = INDIRECT (D13)

0


a source


Through input and further experimentation, I can now state: There is a combination of factors that must be set for an external link to work.

  • If the link refers to another workbook (xref), the other workbook must be open
  • Get rid of single quote characters to use INDIRECT
  • Workbook file name cannot contain spaces

For example, if w1.xls and w 2.xls are open, I can create a new workbook with the following:

A---------B-------C-----D-------------------------E---------------------------------
w1.xlsx   sheet1  $A$1  ="["&A13&"]"&B13&"!"&C13  =INDIRECT(D13)  'will work
w 2.xlsx  sheet1  $A$1  ="["&A14&"]"&B14&"!"&C14  =INDIRECT(D14)  'Does NOT work

At this point, the big limitation of this approach is that all books must be open and the book name cannot contain spaces.

0


a source






More articles:


All Articles