Dynamic Relative Linking-Formula

[SpaaamCatcher]

Hi, there. I have a series of spreadsheets which I am linking to and summarizing in another workbook; all of these files are in the same folder. I would like to be able to replicate this folder for each pay period and automatically have the summary spreadsheet look in its current directory for the files/fields to which it is linked.

I've tested this in clean sheets and I thought I knew how to make it work, but I can't seem to get it right in this pre-existing file. It keeps looking in its previous directory...HELP! It seems simple-I know one of you has this on the top of your head...

Jamie

 

[SkipVought]

aaaaaaggggghhhhhhhh!!!

Multiples upon multiples of data sources -- all similar!

They ALL belong in either ONE TABLE or ONE DATABASE!

Excel is soooooo easy to use and therefore, SOOOOOO easy to make mistakes like putting pay period 1 in a separate sheet/file/folder/server/computer/network/city/state/country/region/continent/planet/galexy...

Ohhh, my head hurts!

This happens in nearly every office that I consult in.

IT IS A VERY BAD DATABASE TECHNIQUE!!!

I've got to go to gazillion different data sources to get a complete picture of wuts heppnin' with the data over time. Maybe it's convenient for a weekly report or sumthin', what what if the big boss asked what the last 5 months' or years' trends were?

It makes it so much better to have all your related data in a single data source. THEN you can report or summarize of analyze by a specified subset of that data...

like a pay period!

I d
   e
    s
     c
      end!

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[SpaaamCatcher]

Well, thanks for your response, I think. I did not architect nor implement this solution, so take it easy. Considering that I'm an Oracle DBA (and NOT an Excel guru), I'm quite familiar with good and bad DB design techniques, so your oh-so-helpful comments don't apply here, since it's not in a database.

We're talking about a sizable hospital; each department has their own spreadsheet into which they enter their daily data. This should NOT be in one spreadsheet-without security that would be a bad idea. The summary workbook is linked to each one for the review of the executives, but my purposes are to backfill the new DB for integrity testing.

Clearly, this data belongs in a database, and I am a key player in a corporate-wide implementation of just such a system. HOWEVER, since we need to resolve this problem, not just complain about how bad the situation is, your comments were quite unhelpful. Over the years I have experienced many contractors like you at offices in which I've consulted; I'm just surprised to find it on tek-tips.

-j

 

[SkipVought]

If you have a template with links that all point to a known folder, one of the easiest ways to "change folders" is to do an Edit/Replace of the folder string on each sheet...

for each ws in worksheets
    ws.Replace What:=sOldFolder, Replacement:=sNewFolder, LookAt:=xlPart, _
        SearchOrder:=xlByColumns, MatchCase:=False
next

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[SkipVought]

Hey Jamie,

You caught me at a bad time -- it's been piling up! I should have been helpful as well. Sorry!

I just see so much of it and it drives me craze. But the bright side is that it keeps me busy as well

Hope my latest post was helpful, if I understood your problem.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[xlbo]

Don't worry 'bout it Skip. We all lose it at some point

Rgds, Geoff
Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?
Want the best answers to your questions ? faq222-2244