dslreports logo
site
 
    All Forums Hot Topics Gallery
spc

spacer




how-to block ads


Search Topic:
uniqs
697
share rss forum feed

nickstoy

join:2001-02-03
Saint-Lazare, QC
Reviews:
·TekSavvy Cable

[Excel] linking to external workbooks

I have to work on a project, and I hit a point where I know there must be an easier solution:

I have a master file with simple formulas, that gathers it's information from 200 excel files.

most of the formulas are exactly the same, except for the fact that instead of referencing "file1", it needs to link "file2".

What I would like to do is simple, but how to do it...is giving me a headache. Could I have a column tell the formula where to fetch the data, another column with the actual formula (using concatenates maybe?)


nickstoy

join:2001-02-03
Saint-Lazare, QC
Reviews:
·TekSavvy Cable

1 edit

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.



Cudni
La Merma - Vigilado
Premium,MVM
join:2003-12-20
Someshire
kudos:13
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


nickstoy

join:2001-02-03
Saint-Lazare, QC
Reviews:
·TekSavvy Cable

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.



RWild
Them Or Us
Premium
join:2003-09-15
Cary, NC

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.



H2OuUp2
Happy to be here
Premium
join:2002-03-15
Oklahoma City, OK

2 edits
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/



2kmaro
Think
Premium,ExMod 1 BC
join:2000-07-11
ColossalCave
kudos:1

1 recommendation

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
 

--
...then THINK! again.

nickstoy

join:2001-02-03
Saint-Lazare, QC
reply to nickstoy

wow! thanks a lot for this code, worked perfectly!



2kmaro
Think
Premium,ExMod 1 BC
join:2000-07-11
ColossalCave
kudos:1

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.