Free Microsoft Excel 2013 Quick Reference

Linking spreadsheets in different folders

I have linked spreadsheets in different folders in my drive with something similar to this formula in a cell:

='[Master Stats.xls]Call Stats'!$b$2,

and get #REF! in the cell, once I closed Master Stats.xls

Obviously, this does not let Excel know where on my C: drive 'Master Stats.xls' and therefore I guess assumes it is in the same folder. I have tried putting the file path in with the spreadsheet name but still get #REF! in the cell. Any ideas please?

Post your answer or comment

comments powered by Disqus
Does anyone know how to get linked spreadsheets in a documentum content
repository to work together? When I open the workbook that is linked to
other workbooks, changes made to the others do not update the workbook I

I seems that linking functions will only worked in a shared unsecured
network folder.


I need help sevearlly on how to automatically re - link my existing excel files which are in multiple folders, and recently moved to a new server (which has a completly new parth names/Server names.). The Files contain no of linkage to other worksheets in different folders.

Is there a faster way of re mapping of these broken links..?, Please help.I simply do not want to open the files one by one and do it mannually


I've searched for the past few hours and cannot find a thing. I have a dilemma and I usually can figure them out by scouring Ozgrid, but not this time. I have 30 students in my class and I have their names in an Excel file called Names. I'm interested in the following:

1. Creating a new worksheet from a list of 30 items I have in Column A of worksheet called Names.xslx. Essentially, it's A2:A31.
2. Then, I need to take a single file I have (the gradebook) and copy it into the new worksheets created above in #1. These are all in different folders.

Location for Names worksheet - C:ChrisOffice 2010NamesNames.xlsx
Location where new worksheets will go - C:ChrisOffice 2010Grade Sheets
Location for single file (the gradebook) - C:ChrisOffice 2010Gradebook.xlsx

Hope I'm not leaving anything out. Thanks for all your help!


Is there any way to link spreadsheets in Excel, so that a value entered in
Sheet 1 is automatically transferred to Sheet 2?


Could anybody please tell me that if I have several .txt files in different folders (like Test0 folder has Test0.txt, Test1 folder has Test1.txt and so on), and I want all these .txt to be shown as different sheets in one workbook (.xls). How will I be able to do it.

Please help.

I need help developing a rather complex spreadsheet.
I am frequently given spreadsheets of lists of records in another application that need to be worked on. After I work on that other application record, I update the current spreadsheet with the date I made the corrections.

I would like to create a statistical spreadsheet that counts the number of records I have worked on per day. To do this, my statistical spreadsheet needs to reference the daily spreadsheets. The hard part is I do not know what the names of these spreadsheets will be in advance.

Can I set-up an external reference to all spreadsheets in a folder?

Something like
Count all cells in the range (G1:G100) in any spreadsheet in folder C:Dailyworksheet that has a date like today's date.

I don't know if it is possible to do such a general search. All help appreciated.


Happy Holloween!

I'd like to copy in VBA a sheet from one workbook to another workbook that
is located in different folder.



I am totally stumped. I have 2 spreadsheets, lets call them Input1 and Input2. There is a lot of information contained in both (several somewhat long sheets). I wish to create an Output spreadsheet which contains selective information from both Input1 and Input2. No problem so far, just copy the cell(s) and use “Paste Link.” OK, so here’s where the fun starts, the problem with doing that is that it only works once, for the files specifically linked (e.g. the full path is contained in the link). What I need is something more generic. Specifically there are approximately 50 client folders (with more, yet unnamed folders being created on an ongoing basis). Each folder has an Input1 and Input2 spreadsheet created by Sales and I need to place an Output spreadsheet in each of the folders. It would take too long and tie me up endlessly, if I try to manually create the Output spreadsheet with manually created links for each folder. I tried removing the long path name so that the link defaults to the current directory but after around 20 attempts with different error messages I gave up. I hope there is an easy fix (FYI – I have a fairly good background in using Excel but I do not know Excel VBA.)

Thank you


I have a workbook A that has links to other workbooks (B,C) in the same folder and links to workbooks (D,E) that reside in a different folder.

I see that workbook A changes the path of the workbooks B and C automatically if the A,B and C are moved to a new folder.

But A is unable to locate workbooks D and E if D and E are moved to a different folder. Is there a way for excel to automatically update the location of files D and E on the machine?

Any help is appreciated.


I have a spredsheet that is intended to gather information from another
spreadsheet. So I have linked the cells. The problem is that for each new
project the linked file is the same name, but in a different folder.

I have an if statement in a cell like the one below.


I need to be able to change "'C:TeklaStructuresModels" to the current
folder. I am collecting the name of the current folder in a cell but I am
not able to replace "'C:TeklaStructuresModels" with the cell or range name.

Is this possible?

Need to create a list of properties having more than 2500 square feet. (Each property has numerous spreadsheets residing in a folder, and all files which pertain to that property are located in that Property's folder name.) Each of the property details are in the same cell, but each "xxx Location Details".xls spreadsheet is in a different folder. Need to return the contents of that particular cell, or data from other cells, as well as the name of the sheet that it was found in.
Looking to search for specific cell contents in any folder which contain files and as part of the file name:
To Recap:
Parent folder: "Properties"
Subfolders: "xxx"
Filename contains: "xxx Location Details"
Cell A1, and Cell B3, and C3 (for starters)

Thanks in advance for any thoughts on this. One caveat, the "xxx Property Details" spreadsheets are not arranged in a grid, so creating a database on the quick would not be an option.

I have a spredsheet that is intended to gather information from another
spreadsheet. So I have linked the cells. The problem is that for each new
project the linked file is the same name, but in a different folder.

I have an if statement in a cell like the one below.


I need to be able to change "'C:TeklaStructuresModels" to the current
folder. I am collecting the name of the current folder in a cell but I am
not able to replace "'C:TeklaStructuresModels" with the cell or range name.

Is this possible?

I created a workbook with 5 or 6 tabs that all link to each other. I then seperated them and saved them in different folders on our server. Has anyone done this before? Will there be any problems with it? I am already having the following issue...

When I open the different worksheets all of the info is there. When another employee opens them not everything is populated even after they hit the update button. Why would it do that?

Here's my situation:

I have a group of folders that is routinely copied to an alternate
location. There are excel files contained within; some of them in the
main directory path, some within subfolders.

I've tried linking these excel workbooks together; however, when I
copy the directory tree, the only files that automatically update
their links are the ones that reside in the same folder. The files
that reside in a subfolder won't automatically update.

Is there a way to point Excel to a workbook using a relative link,
instead of the absolute link it uses by deafult? I know I can go into
each excel file and use Edit > Links, but I'm looking for a method
that won't stress our less computer-savvy users.


I have been working with a few spreadsheet in different folders, most of
which are inter-linked. When opening a new spreadsheet, Excel would ask to
update links, which proceeded without issue.

However I recently re-organised my folders in a more logical manner. Of
course, this meant that Excel could no longer automatically find to the sheet
it was meant to link to. I subsequently found each sheet and updated the
links manually. This also proceeded without issue.

The problem now is, that every time I open a spreadsheet, it cannot find the
sheets that it is supposed to be linking to! Every time I open a sheet, I
have to point it to the directory that it's linked sheets are in, even though
they haven't moved for ages! How can I fix this? It's quite annoying, and
seems like it should be easy to fix considering that the files are stationery

I've tried using "Edit", "Links" and updating all the links manually that
way, but it doesn't seem to work. Please advise.

Thanks for your time,


PS - the files are located on a network drive if that helps.

Hi, I have a group of files that are being updated separately, but all
reside in the same folder. There is 1 consolidated file in that same folder.
I need to create an update every week for the same set of files, which I do
by copying the entire folder to a new folder and update the files.

Is there a way that I can have the consolidation file pull from the files in
the folder that the consolidation file is in? I.e. I would prefer not having
to go in that file and manually change all the links.


I have several workbooks in different folders. There are 85 worksbooks in total. I need to get figures into these sheets from five different other documents. If I was to manually link all of the documents then I have worked out that I would need 9340 individual links! I was wondering if I could write a macro that looks for the name of the person cross matched with the name of the category and the value that is in the corresponding cell. Any Ideas??

Got a macro which I'd like to make a little more flexible. Currently, I have a "Menu" tab which has the full path of files in Column A. My macro opens each, copies data from them, then closes them.

The issue I have is that I manually need to enter the full path for each. Since the file names are similar (they all start the same) but are in different subfolders, is it possible to set up a macro which will look in a folder (and it's subfolders) for a file that begins with a certain string?

For example, I have the following:

U:FS2008208LAM load filesBilledUnits_0208_03.05.08.xls
U:FS2008308LAM load filesBilledUnits_0308_04.06.08.xls
U:FS2008408LAM load filesBilledUnits_0408_05.06.08.xls
U:FS2008508LAM load filesBilledUnits_0508_06.04.08.xls

but would much prefer to enter a folder (such as 2008) and have it enter and open files in that folder or subfolders, that begin with a cetrain string (like BilledUnits)

Is this an easy thing to accomplish or am I opening up a huge can of worms?

I have 2 spreadsheets in Excel 2003. Spreadsheet A has links to data in
spreadsheet B. If spreadsheet B is closed when I open Spreadheet A, I am
asked if the links should be updated. If Spreadsheet B is open when I open
spreadheet A, I am not asked if the links should be updated.

Why am I only asked if updates should be made if the linked to spreadheet is

Thanks in advance for clearing up that which is cloudy for me.
Have a great day.

Hello, I am new to this forum, so hello to everyone, nice to be here!

I have a project that I am developing in Visual Basic 6 and part of the project requires opening, modifying and close a excel spreadsheet. I have the code and in general works fine and without problems.

However one problem has arisen, when I try and open a excel spreadsheet that is in a folder that contains a space in the folder name, it won't open the spreadsheet. I have included the code below

    Dim oExcel As New Excel.Application
    Dim oWorkBook As Excel.Workbook
    ' before we can do anything, we need to check the excel file exists
    If IsFile(sExcelFile) Then
        ' excel file exists, proceed and open it
        oExcel.Workbooks.Open FileName:=sExcelFile

        ' do what i have to do.......

    End If
I have tried to enclosed file name in quotes and that doesn't work

Is there a way to save a backup copy od xl2k workbook in a DIFFERENT folder
than the original? With mutliple workbooks folder becomes too cluttered.


hi ,

i am new to the board and also new to VBA , i was wondering if someone could help me in this following problem in VBA code:

here is the problem description:

I have two spreadsheets in different workbooks ( workbook 1: sheet 1 and workbook2: sheet1), here i need to compare column 5 in Book1 and Column 5 for all cells, say X is the value we are looking for..

X occurs once in book1 and might occur more than once in if a match occurs ( that is once the code checks that there is X occuring in both books in columns 5) it should copy all rows in book 2 where X occurs to a new workbook 3 in sheet 1 and also it shoud copy entire row data where X occurs in book 1 sheet 1 . But this data from book 1 has to be copied at the end of row after the data from book 2 has been copied.

if X occurs 4 times in book 2 , then 4 rows have to be copied in book 3 and then data from Book 1 where X occurs only once is copied 4 times at the end of the data from book 2.

this process has to repeated for all cells in columns 5 in book1 and column 5 in book2 .

i just started on the code and tried my best of programming skills which is not that great i guess (

i 'll be grateful if someone can help me on this..below is my code:

    Dim M, N As Range, x As Variant, y As Variant 
    Dim NewRange As Range 
    ‘ To get the book1 location 
    MsgBox " Selec the Location of N File" 
    Application.Dialogs(xlDialogOpen).Show arg1:="" 
    Set N = Columns("E") 
    ‘ To get book 2 location 
    MsgBox "Select the Location of M File" 
    Application.Dialogs(xlDialogOpen).Show arg1:="" 
    Application.ScreenUpdating = False 
    Set M = Columns("E") 
    ‘ this Is where I am stuck bigtime.!!!!!!!!!!! 
    For Each x In M 
        For Each y In N 
            If cell = y Then y.Offset(0, 1) = y 
            Set NewRange = Union(Worksheets("sheetB").x.EntireRow, Worksheets("SheetA").y.EntireRow) 
            Set NewRange = Nothing 
        End If 
    Next y 
Next x 
‘ this opens the 3rd work bookbook 
Selection.PasteSpecial Paste:=xlValues 
Application.CutCopyMode = False 
Application.ScreenUpdating = True 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Is there a way to save a file in one folder and then save as in another folder using a macro. How do I do this?


I guess I don't know if there is a real term for this, but here is our problem. We have a database of about 100 rows of customers with about 15 columns of specified information. We need to copy this information, 1 row at a time, and paste it into an excel form in different cells. Is there a way I can name the columns so I can copy a row and paste it in the first cell (ie name) of the excel form? I created a mock example. Thanks and much appreciated.

No luck finding an answer? You could always try Google.