Linking Workbooks ...

Apr 4, 2007

I run a number of spreadsheets each 4 week period and these are stored in a folders named after the period. ie Period 10 will contain workbookA10wk1, workbookA10wk2, workbookA10wk3 and workbookA10wk4,

Period 11 will contain the same workbooks but named workbookA11wk1 etc.

The data from the workbooks is then used in graphs / tables for each year.

I always pick up the same cell ref in each workbook to compare each week and my table is set out with heading of period and wk.

Is it possible to use the table headings to produce the names of the workbooks that you wish to reference, ie: ='Period 13[workbookA13wk1.xls]Sheet1!$D$17 to appear under Period 13, wk1

View 9 Replies


Linking To Other Workbooks

Jun 20, 2014

It is quite standard to create links between workbooks, and generally I do this by inserting an "=" sign in the cell I want data to appear in for Workbook 1, I then open workbook 2 where the desired data is and click on the cell housing the data I want.

I just went to do this, and upon putting the = sign in the cell I want in workbook 1 and clicking in workbook 2 nothing happens except my cursor is now in workbook 2 and the = sign just remains alone in the workbook 1 cell.

If, however, I put an = sign in a cell in workbook on and then click another cell in that sheet or a cell in another worksheet but in the same workbook a proper link is created, so the problem seems to be isolated to links to other workbooks.

View 1 Replies View Related

Linking Data Between 2 Workbooks

Jul 16, 2014

You have two Workbooks open. Workbook 1 and Workbook 2.

You COPY all the data from Workbook 1 and PASTE the data as a 'Link' into Workbook 2.

You then save and close both Workbooks.

You then open Workbook 1 and edit the data. You save and close Workbook 1.


Will the linked data in Workbook 2 update automatically (i.e., without opening the file)?

View 4 Replies View Related

Linking Lists Across Workbooks

Jan 20, 2009

I am trying to create a master price list, where the 1st file called MasterPriceList will list all of our ingredients and their prices.

It would look like this:
1 Ing 1___$1.00
2 Ing 2___$1.50
3 Ing 3___$2.00
4 Ing 4___$3.00

My second workbook is is a template for when we need to formulate blends.
It looks something like this:
1 Ing 1__50%__$1.00__$0.50
2 Ing 3__50%__$2.00__$1.00

We want to pull the cost of the ingredient from the MasterPriceList and populate column C with that value.

I have tried copying and pasting a link. This works fine until I insert a row. Even If I make the link relative (='[MasterPriceList.xls]Sheet1'!$C1), it will only adjust if both worksheets are open at the same time. Since we will have over 200 pricing sheets, it would be impractical to have them all open every time we have to insert a new ingredient into the MasterPriceList workbook.

I have tried to use VLOOKUP but it will not work across workbooks and the same is true with Drop Down lists. I read a tutorial, , which details how to create a drop down list across workbooks but when I come to the step of defining the validation list, I get a message saying that it can't be done across workbooks.

View 4 Replies View Related

Linking To Other Workbooks W/ IF Statements

Aug 8, 2008

I have created a link in workbook "A" to another workbook "B" by copying the cell in workbook "B" and using "Paste Special" and "Paste Link" in workbook "A". I did this for 1,000 rows so that when I update the workbook "A", it will take in any new data.

However, if a cell does not have a value in it, the cell in workbook "A" returns a "0".

To avoid this, I figured I could use a statement like =IF('C:[B.xls]tabname'!A45="","",'C:[B.xls]tabname'!A45).

It worked in the case of worksheets, but in others, it only returns "#########". If I hit "F9" it doesn't refresh the data to return the value, so I assume something silly is going on here.

View 9 Replies View Related

Linking Worksheets In Different Workbooks

Jul 7, 2006

I have three workbooks stored in the same drive(shared drive). Let's call them 1.xls, 2.xls and 3.xls. Each workbook has one sheet.Column A in all sheets is the same. I want to make the following linking:

-Column A of the sheets in 2.xls and 3.xls to be linked and get data from column A of the sheet in 1.xls.
-Column B of 1.xls to be linked and get data from column B of 2.xls and column C of 1.xls to be linked to column B of 3.xls.

So far it sounds easy. What I cannot find is what to do when I insert a row in the sheet in 1.xls and write something in column A. I managed from DATA- IMPORT EXTERNAL DATA to refresh column A of the sheets in 2.xls and 3.xls, so that these columns contain the updated information. I cannot do the vice-versa procedure: for example, to insert something in column B of the sheet in 2.xls and refresh the values of column B of the sheet in 1.xls. The fact is that when I insert a row in 1.xls the right references get lost and move one cell up. I want the references to remain stable. In a way the sheets are interdependent.

View 2 Replies View Related

Linking To Password Protected Workbooks

Jan 24, 2012

I have all of the passwords. I just want to avoid having to type in all 30+ of them each time I open the master sheet.

I have a master for each manager that pulls a few values from each of their employees personal sheets.

The personal sheets (about 30) are individually password protected so only the owners can open them.

This is creating a security issues as the links in the master require the passwords for each of the "source" workbooks.

Is there any way to add some code so that the passwords do not need to be entered each time?

View 1 Replies View Related

Linking Multiple External Workbooks

Apr 12, 2007

I have a workbook called Pricing.xls that contains *multiple* external links. The Pricing WB summarized monthly data, the trick is that each month is in a different WB (*DEC06.xls), and the big problem is that there are dozens WBs a month.

I am using vlookups to get the data from external WBs.

The way that it is being done currently is; the formula is copied across month to month, and them manually editing the link in the formula bar. This seems to half work, but causes Excel grief. Is there a way to link to multiple work books without manually linking them each time?

View 9 Replies View Related

Linking To Workbooks Whose Filename Is In A Cell

May 9, 2006

One folder on the drive contains excel files, which are created daily from a template. I use these files, the 20 most recent days' worth, to create an analysis file. This new file is organized in rows, starting with the date. The files I am using are all saved in the form "Stats yyyy mm-dd.xls" The first line of each row of the analysis spreadsheet starts with the date, in the format "yyyy mm-dd". Is it possible to create a macro, or in another way, for the analysis spreadsheet to automate the reading-in of data from these different files?

View 2 Replies View Related

Linking Cells To Closed Workbooks

Aug 9, 2006

I have multiple workbook files using the same template but saved under different files names usually by date. I need to summarize the data in a summary worksheet that pulls the same cells from the various individual closed workbooks. I need to be able to insert the file's path as an input to pulling data from a cell or cells.

View 8 Replies View Related

Linking Names To Closed Workbooks

Feb 14, 2007

I've named some cells in the source workbook, and linked these names to the destination workbook. Then I used the names in some formulas in the destination workbook, and it worked great. But when I closed the source workbook it doesnt work. WHen I open the source it works great, but I need for it to be able to link to the workbook when it is closed as well.

View 9 Replies View Related

Linking Multiple Workbooks To One Master Workbook

Dec 27, 2012

At our small firm, each staff member keeps a small excel spread sheet on the network that contains their "work in progress." This spreadsheet is roughly six columns wide and roughly twenty lines long.

What I am attempting to do is have one "master" spreadsheet with a tab for each staff member that links their "work in progress" so that partners and managers can easily see how much work each of the fifteen or so staff members have.

I can create fifteen different tabs and fifteen different files on the network and link =[Staff1.xlsx]Sheet1!$A$1:$G$25 =[Staff2.xlsx]Sheet1!$A$1:$G$25 =[Staff3.xlsx]Sheet1!$A$1:$G$25 and so on.

If I make a change to the layout of the work in progress sheet, I have to delete and copy the file fifteen times, then go back through and re-link fifteen tabs to fifteen workbooks in order to keep it uniform.

View 1 Replies View Related

Linking Macro Workbook To Other Workbooks That Use Macros

Jan 10, 2014

I am trying to implement the idea of creating an application with two workbooks: a hidden code workbook (CodeWb) that holds all of the forms and macros, and a separate visible data workbook (DataWb) that uses the macros in CodeWb. The DataWb houses a little bit of VBA code to open the CodeWb, which then supplies the programming power to handle click events, etc., performed by the user in the DataWb.

I have advanced to the point of being able to open CodeWb from DataWb and trigger execution of a macro named "DataFileOpen" in CodeWb. This macro initializes things in DataWb and creates buttons there on a particular sheet, using the line:

Application.Run "CodeWb.xlsm!DataFileOpen"

So far so good. This works fine. My problem is that nothing happens when I click on the buttons created in DataWb, with their event handling code residing in CodeWb. Obviously, I'm missing something that maintains a connection between the two workbooks.

How can I use the code in CodeWb to handle all events occurring in DataWb?

View 2 Replies View Related

Copy Worksheets From A Couple Of Workbooks To Other Workbooks

Jun 26, 2014

I have 25 files with certain worksheets that I need to move to 25 other files.

Worksheet 1, 2, 3 and 4 in Workbook A needs to be moved to Workbook A-2014 Worksheet 1, 2, 3 and 4 in Workbook B needs to be moved to Workbook B-2014 Worksheet 1, 2, 3 and 4 in Workbook C needs to be moved to Workbook C-2014 etc....

Is there a way to do this with a macro? Preferably I would like to do this automaticly - i.e. runing the macro from a master file that

1. Opens Workbook A copies the worksheets
2. Open Workbook A-2014 paste the sheets
3. Save and close Workbook A-2014
4. Close workbook A without saving

then doing the same for Workbook B, etc.

View 5 Replies View Related

Close Workbooks And Move In And Out Of Workbooks In VBA

Dec 27, 2009

Basically the main workbook opens 2 files at a time performs a calculate in the main workbook and then copies and pastes information in 3 ranges. Then closes the two open workbooks and loops and performs same operations until it hits the maximum loops. My macro is as follows and I have 3 question in capital letters.

View 3 Replies View Related

Validation And Linking

May 27, 2008

I've attached a basic excel file with an example of the problem I need to solve. As you can see, if you select "No" to "Do they have a pet?" then conditional formatting blocks out the next field. The problem with that is, it still allows you to enter a value into the "Type" field which is skewing my results. Is there a way to allow a value in "Type" ONLY if there is "Yes" in "Do they have a pet?". I would also like it to delete the value in "Type" if I later select "No" instead of just covering it up. Is this possible? I'd like to avoid the scripting route if at all possible but I'm not sure it is...

Also, I have two linked spreadsheets, one pulls the totals off the other. However, If I add rows into the original it does not update the total formulas in the second sheet. Is there a way to make it do this automatically?

View 10 Replies View Related

Linking Two Spreadsheets

Aug 5, 2008

I am working with excel2003. I have to different workbooks in which on is a Bill of lading that is used to show the items being shipped. The other workbook has a list of the items that we ship.

On the BOL I want to be able to type in the "L" number (or I.D. number) in D14, D19, D24, D29. By typing in the L-number I want it to automatically bring up the product name into cells E14, E19, E24 and E29. With the exact name of the item it will help prevent employee error. I want it to also bring over the exact weight over from the flavor list in cell range C5:C28.

I have the FlavorCodes range with all the L-numbers on the BOL in cell range U9:U48. I thought I had it set up correctly but when I select the L-number in the drop down list in cells E14, E19, E24 and E29 I get error code #NA. I thought I defined the cell range but it is still not working.

The workbooks are attached below.

View 12 Replies View Related

Linking Worksheets ...

Dec 1, 2008

I have a problem and wanted to know if there was an easier way to create links to various worksheets. I have a folder with 59 worksheets. The names of the Worksheets are the weeks of the year(example LA-01-01-09) I have another workbook with 13 worksheets one for every month and a sheet that totals the whole year up.

What I want to know is how can I create a link to everyone of those weekly sheets to go to my monthly sheets,so when data is entered into the weekly sheet the monthly sheet will update automatically? The weekly sheets are setup exactly the same. I'm looking only to pull row 3 columns c:d:f:g:h:i over to my monthly sheet.

I have an example of my weekly sheet and monthly sheet.

View 6 Replies View Related

Linking A SUB To Another Module

Jun 9, 2009

I have a Form that when the 'SUBMIT' button is clicked, I would like to validate the information and then write it to a 'MASTER SHEET'. However, the validation has gotten lengthy and I have opted to put the validation code in a separate Module to keep my general code a little cleaner.


The original code would kick you back to the form if something was wrong via a 'END SUB', but as now the SUB is on a different Module, it isn't working.

Here is the pertinent code on the general code sheet: ....

View 13 Replies View Related

Linking Worksheets

Oct 13, 2005

I want to pull data from about 100 different Excel files. They are located in
the same folder and drive and the data I want is in consistent cell
locations. I would to find a fast way to reference the files. something like
S:FolderJanuaryCaseNumber1.xls that will allow me to pull-down the
folumula and copy the month and case number from two separate fields on my
spreadsheet. So I would specify the month and case numbers and have them
automatically populate my link formula.

View 11 Replies View Related

Linking Two Formulas

Mar 20, 2009

in each cell I have formatted a Workday formula which is for example:

Now I want to add to that function
"If D16 is blank return blank"

What I am getting at the moment is that the if D16 is blank I am getting #VALUE!
THis makes the sheet very hard to read.

View 14 Replies View Related

Linking Buttons With A Row?

Nov 23, 2011

If possible I would like to create using vba a button at the end of my rows of data allowing a user to copy and insert the row next to the button, i.e. duplicate.

I am using the following code to create the button but am not clear how to link a button to a row or cell. The button would be within a cell in a row at the end of the data.

Range("a2").Offset(rowoffset, 10).Select
ActiveSheet.Buttons.Add ActiveCell.Left, ActiveCell.Top, ActiveCell.Width, ActiveCell.Height

rowoffset increments within a loop and a button is placed at the end of each row of data.

I would also like to know how to name the button.

View 5 Replies View Related

Linking VB To Another Notebook

Jun 27, 2007

I am working on a project and if i hit button one in my VB program i need it to pull up a program under c:programfiles/user/name.xls

View 9 Replies View Related

About Linking Cells?

Sep 9, 2007

how do I go about linking cells?

Lets say I have a workbook with 6 sheets on it and the sheets names are:


And we will just use cell A1 through sheet1 to sheet5 and the status sheet to grab the numbers from sheet 1 through sheet5
Sheet1 through sheet5 will have numbers in cells A1

Sheet1 : 586
Sheet2 : 436
Sheet3 : 621
Sheet4 : 610
Sheet5: 561

And in the status sheet I would like to find the highest number from these 5 sheets and put it in cell A1 status sheet and have it linkable like when it finds the highest number it will put it in cell A1 on the status sheet and when I click on the A1 cell in the status sheet I would like it to goto that sheet that has the highest number it found and maybe highlight it or something.

View 9 Replies View Related

Linking To Another Tab Causes To Crash

Nov 16, 2009

I have a multi-tabbed spreadsheet that keeps crashing when I try to set up a reference from one tab to another tab in the same spreadsheet (e.g., =Sheet2!B1) . The spreadsheet does NOT have any macros in it although my Personal.xls DOES have numerous macros that I have been using for over 6 years with no problems. In any ase, when I start Excel without loading personal.xls, so that NO macros are loaded, the spreadsheet still causes Excel to crash when I try to set up the reference to another tab.

View 9 Replies View Related

Linking To Other Spreadsheets

Jan 4, 2010

Is it possible for me to link to other spreasheet data, and have the links update (retrieve the data) without those files being opened?

View 9 Replies View Related

Linking Another Workbook

Sep 18, 2006

I have a workbook Book1 that has 4 sheets, one of those sheets needs to be an exact replica of a sheet from another workbook Book2. I tried to directly link the data and use Vlookup's but the problem is that when someone opens Book2 and inserts more data (Inserting rows) it does not show up on Book1.

View 3 Replies View Related

Linking Spreadsheets

Jan 14, 2007

I have sucessfully created a spreadsheet which links to another using a vlookup and an array. I now need to reverse the process. My requirements are this:

I have individual spreadsheets which represent individual projects/quotes for our customers. I'd like each of our sales staff to have a summary spreadsheet which summarises all their ongoing projects in one spreadsheet, dynamically.

when a new project is created on a new (projects/quote) spreadsheet, it looks to the summary spreadsheet and looks to see if that project exists in the summary, if not, it creates a new line and writes the summary details to it. then as the project spreadsheet evolves, it updates its details on the summary sheet dynamically.

View 3 Replies View Related

Linking The Worksheets

Mar 1, 2007

I compile a report each month. I have a program/macro built to do this so based on the month I select it runs the code below. The code below would run if i selected January. I run this report on a spreadhseet and its for a ton of people, and my code is really long. I know there is someway to shorten it, but i cant figure out how. Below is an example of one persons, and the next persons would begin on the row below the example.

Sub January_NB()
Sheets("New Business").Activate
With Sheets("New Business")
ActiveCell.FormulaR1C1 = _
"='\lm-file-dfs-01centralPermktUnd-Prod-CtrsProperty Count SheetsNew Work ReportNB Team Count sheetsChristie[Count Sheet January Christie.xls]Tally'!R73C4"
ActiveCell.FormulaR1C1 = _
"='\lm-file-dfs-01centralPermktUnd-Prod-CtrsProperty Count SheetsNew Work ReportNB Team Count sheetsChristie[Count Sheet January Christie.xls]Tally'!R6C4"

View 4 Replies View Related

Linking A Table

Apr 15, 2007

i have created a worksheet in excel that is used to moniter employees. i want to export the data in to an access database when a button is clicked so that i am able to track the employees over a period of time. how can i do this using vba?

View 5 Replies View Related

Copyrights 2005-15, All rights reserved