Tell me more x
, there is a new speed test available. Give it a try, leave feedback!
dslreports logo
 
    All Forums Hot Topics Gallery
spc

spacer

Search Topic:
uniqs
795
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- ··· 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/eng ··· ortwbado

And then look here: »dailydoseofexcel.com/arc ··· 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.

--
...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.