Linking WorkBooks across Directories

[jacobpressures]

I found this information on Microsoft's website but i don't fully understand it because it is giving me trouble.

Network drive letters in links When you click a cell containing a link and the source workbook is closed, you'll see the full path to the source workbook. The path uses the drive letter you have mapped to the share. The links continue to work correctly for other users who map different drive letters to the same share.

You can edit linking formulas to use UNC names, such as \\myserver\myshare, instead of drive letters. UNC names can make links easier to update when several people will use a linking workbook, because Excel can update links that use UNC names even if users don't have a drive letter mapped to the network share. For example, you can change ='G:\[source.xls]Prices'!B5 to ='\\myserver\myshare\[source.xls]Prices'!B5. However, using UNC names can also reduce calculation speed, especially if your workbooks use calculation-intensive features such as the Solver add-in program.

I'm QUITE A FEW (maybe about 100 workbooks) that will be connected based on department and center.

A few problems. Once i do a department, i plan to save ALL SIMILAR Departments in different CENTER FOLDERS.

I've been afraid to use C: etc because i don't want to have to redo all my links since i don't know what letter or where exactly from the root the files will be placed on the server.

The other problem is that everytime i used teh "='\\myserver\myshare\[source.xls]Prices'!B5" type link, a dialog box pops up asking me to go directly to the file i want to link to. Otherwise it gives me a REF error. I can't possibly go to all these files. I need to be able to cut and paste these paths and maybe use CTRL + F (for Find) and do minor maintenance.

Any suggestions? I assume the more generic version was created to make it more portable across teh nextwork with out all the links breaking down, but i can't figure it out.

thanks!

 

[SkipVought]

Hi,

My reference works and looks just like yours...
[tt]
='\\myserver\myshare\[source.xls]Prices'!B5

='\\dfwsrv222\public\SkipM\[CellBigHitters.xls]Sheet2'!A2
[/tt]
What is the ACTUAL NAME of your server????

Skip,

_{[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine!}

 

[GlennUK]

You say:

I've been afraid to use C: etc because i don't want to have to redo all my links since i don't know what letter or where exactly from the root the files will be placed on the server.

The letter being used doesn't matter as much as the folder hierarchy between reporting workbook and supporting ( linked ) workbooks. If the hierarchy means that the supporting folders are on the same level or lower than that of the reporting workbook, then copying your workbook onto a network ( together with all the supporting workbooks, in the same hierarchy ), will work without any changes.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[jacobpressures]

thanks but why am i having to browser to find the folder everytime i add a path??? this is the problem!

I'm not currently using a server. I'm using my WinXP computer until i get finished. I'm trying to avoid all the steps of browsing for the folder just to make a connection. Is this normal? Is there a way arround it? I'm sure i'm not doing something right.

this is a cut and paste from microsoft. that is not my information.

='\\myserver\myshare\[source.xls]Prices'!B5

 

[jacobpressures]

Guys i really need help with this. Is it supposed to prompt me to browser to find the folder for each adn ever link to another workbook?

 

[SkipVought]

The other problem is that everytime i used teh "='\\myserver\myshare\[source.xls]Prices'!B5" type link, a dialog box pops up asking me to go directly to the file i want to link to

I'm not currently using a server.

so are you using it or not?

Skip,

_{[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine!}

 

[SkipVought]

Have you checked out Glenn's suggestion regarding relative path (same heirarchy)?

Other than that, what is the process that you are having to go thru that is so taxing? Please explain in enough detail and clarity that someone who knows nothing of your situation would have a crystal clear understanding of your problem.

Skip,

_{[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine!}

 

[jacobpressures]

Thanks guys. This is frustrating.

To describe what i do isn't that difficult.

In a folder called Daily Encounter Balance Sheets is a folder for each doctor's balance sheets.

Therefore you have a Daily Encounter Folder and a folder for DoctorA, DoctorB, DoctorC each with their own spreadsheets. They have a set of spreedsheets that calculate for the week grouped in months. A book completes the month. In the doctors' folder, there are spreadsheets for each month of the year.

so you have Daily Encounter Balance Sheets folder, a
Folder for each doctor which holds a workbook for each month.

All the doctors for that center are aggregated together in a spreadsheet listing all the providers for that center.

To clarify the location of the folder hierarchy
There is a Daily Encounter Balance Sheet folder with the above info and a Center Spreedsheet that links to all the Providers' (or doctors') folders. The center spreadsheet is NOT in a folder of its own but in the Daily Encounter Balance folder beside the doctors' folders.

When i try to put in a path
='\Powe, Ollie\[January 2006 Daily Encounter Balance Sheet.xls]Monthly Totals'!L10

I don't add the folder (Daily Encounter Balance Sheets) that encloses everything but it appears to make no difference anyway.

Excel converts it as
='C:\Powe, Ollie\[January 2006 Daily Encounter Balance Sheet.xls]Monthly Totals'!L10

but Dr. Powe's folder is NOT directly within the C: root.

Once i press enter to enter the path into the cell, a dialog box pops up and i have to trace where the file can be found. When i find the file, i double click and the dialog goes away. It appears that the path doesn't matter. I could make the path say anything and it seems to work as long as i trace where the file is. It doesn't have to be accurate or anything which seems to me is a problem. Something is not right.

SO even though it pulls the info from that file
1) the path seems to be ignored

2) I can't possibly trace each file using the dialog box each time i want to link a cell to another workbook. it would take me a year just to pull a page of information. I have approximately 20 providers with 12 months apiece.

3) Each time changes are made to the orginal workbook and the workbook with the external links is opened, a message box displays asking to update links. Our users can't do this just to see updated info each time the file is opened.

What i would like to do is paste in links into the cells and save them. When i open the book, it automatically updates without having to reconnect to previously linked(unmoved) workbooks.

I will be happy, IF...
1) I could get around browsing for the workbook file and just paste it in like we do for worksheets within the same workbook. tiis is easy and less time consuming.

2) The links actually worked when i reopened the file.

Thanks for your help and patience.

 

[SkipVought]

I have a link to
[tt]
='\\dfwsrv222\public\SkipM\PWA\Level1\Level2\[WorkbookA.xls]Sheet1'!A1
[/tt]
I want to CHANGE the path of a share from \PWA\ to \SIOP\

Simply do an Edit/Replace.

All the link change. There's no dialog to answer.

Skip,

_{[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine!}

 

[jacobpressures]

Thanks skip! I'm going to try this on a few other computers. Each time i press enter, especially if i make a change, a dialog comes up which you just said doesn't happen. Let me try a few different systems just to see what happens.

thanks!

 

[SkipVought]

Then you don't have a valid link reference!

I keep seeing you with ONE LEADING BACKSLASH???

If you're not starting with a SERVER, then you need the local drive path, since you earlier stated...

"I'm not currently using a server. I'm using my WinXP computer until i get finished"

Skip,

_{[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine!}

 

[jacobpressures]

That's it! It nolonger prompts me!

But if i use the whole path will i have problems when i move the documents to the server?

One other question if you know the answer. Why does the computer differientiates between a desktop and server?

THANKS A MILLION SKIP!!!!!!!!!!!!!!!!

 

[SkipVought]

It's just a path.

Skip,

_{[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine!}