OS and Software > Microsoft Help > [Excel] linking to external workbooks

[Excel] linking to external workbooks

I see a lot of uniq views, so I'm guessing no one has an answer, or my question isn't clear enough...I hope for the later.

Is there a way to have cell A1 =[c:/file1.xslx] sheet1! A1

And use cell B1 to tell cell A1 what file to use? So if I change b1 to [c:/file2.xlsx], the formula in a1 changes accordingly?

Thanks

**I tried using the INDIRECT function, but for that to work, I need to have all 200 files opened...not happening.

reply to nickstoy
a matter of syntax, see
»office.microsoft.com/en-gb/excel···338.aspx

Cudni
--
"what we know we know the same, what we don't know, we don't know it differently."
Help yourself so God can help you.
Microsoft MVP, 2006 - 2012/13

That explains how to do external references. That's not my problem. What I want to do is change the external reference by using a lookup, or a cell value.

Is there a way to have cell A1 =sheet1!A1

And use cell B1 to tell cell A1 what file to use? So if I change b1 to sheet2!A1 the formula in a1 changes accordingly? (switches sheet1! to sheet2!).

Again, I cannot use INDIRECT, because I'm looking 200+ files and I can't have them opened.

Sounds like you need to use VBA.

If the 200+ files have sequential names, it would be easier. In this case, you could construct each file name inside a loop as a text variable, the use concatenation to build the complete file name, activate the file, and pluck out the value you want, close the activated file and step to the next.

If the file names are not sequential, have the macro stick them in an array and use each element to build the string containing the complete file name.

reply to nickstoy
NM,

I see you didn't want to use Indirect.

I would start here: »www.erlandsendata.no/english/ind···ortwbado

And then look here: »dailydoseofexcel.com/archives/20···rksheet/

reply to nickstoy
Here's some worksheet event VBA code that will do the job for you based on your description of what you're going to have the user enter into column B.

To put the code into the proper place in your workbook:
Open the workbook, select the sheet where this function needs to work, RIGHT-click on the sheet's name tab and choose [View Code] from the popup list. Copy the code below and paste into that module. You can change any of the 3 Const declarations to set it up for a worksheet layout. Repeat for as many sheets as needed.

Private Sub Worksheet_Change(ByVal Target As Range)
'change these to adapt for different column use
  Const formulaColumn = "A" ' column with formula to be changed
  Const userEntryColumn = "B" ' column user will enter new path/file info into
  Const firstUserEntryRow = 2 ' first row with user entered info
  
  Dim tempFileName As String
  Dim checkForFile As String
  Dim currentFormula As String
  Dim sheetCellRef As String
  'if changed more than one cell at a time, or
  'if not in user entry rows, ignore and if NOT in user entry column, also ignore
  If Target.Cells.Count > 1 _
   Or Target.Row < firstUserEntryRow _
   Or Target.Column <> Range(userEntryColumn & 1).Column Then
    Exit Sub
  End If
  'get user entry, remove leading/trailing blanks
  tempFileName = Trim(Target)
  If InStr(tempFileName, "[") = 0 _
   Or InStr(tempFileName, "]") = 0 Then
    MsgBox "The filename must be enclosed in square brackets;i.e: [file.xlsx]", _
     vbOKOnly + vbCritical, "Invalid entry"
    Exit Sub
  End If
  'is the file available?
  'remove [ and ] to make a path out of it
  tempFileName = Left(tempFileName, InStr(tempFileName, "]"))
  tempFileName = Replace(tempFileName, "[", "")
  tempFileName = Replace(tempFileName, "]", "")
  checkForFile = Dir(tempFileName)
  'refresh the entry
  tempFileName = Target
  If checkForFile = "" Then
    If MsgBox("File '" & tempFileName & "' is not available." _
     & vbCrLf & "Use this entry anyway?", _
     vbYesNo + vbQuestion, "Path Not Available") <> vbYes Then
      Exit Sub
    End If
  End If
  currentFormula = Range(formulaColumn & Target.Row).Formula
  sheetCellRef = Right(currentFormula, Len(currentFormula) - _
   InStr(currentFormula, "!") + 1)
  'build new formula
  Range(formulaColumn & Target.Row).Formula = _
   "='" & tempFileName & "'" & sheetCellRef
End Sub
 

reply to nickstoy
wow! thanks a lot for this code, worked perfectly!

Glad to hear it worked for you. One nice thing about it is that while manual changes are handled only one-cell at a time, if you use Edit --> Replace to change the entries in column B, it will modify all of the formulas associated with cells in column B that get changed. This would allow you to change a workbook or sheet name entered into that column several times with a single action.
Enjoy!
--
...then THINK! again.