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.
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.
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
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: A_________B ING______Price 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: A______B______C_______D__ 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, http://office.microsoft.com/en-us/ex...995141033.aspx , 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.
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.
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?
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?
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.
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.
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.
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?
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
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.
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.
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() 'Week1 'Christie Sheets("New Business").Activate With Sheets("New Business") Range("B8").Select ActiveCell.FormulaR1C1 = _ "='\lm-file-dfs-01centralPermktUnd-Prod-CtrsProperty Count SheetsNew Work ReportNB Team Count sheetsChristie[Count Sheet January Christie.xls]Tally'!R73C4" Range("D8").Select ActiveCell.FormulaR1C1 = _ "='\lm-file-dfs-01centralPermktUnd-Prod-CtrsProperty Count SheetsNew Work ReportNB Team Count sheetsChristie[Count Sheet January Christie.xls]Tally'!R6C4" Range("E8").Select...........................
I create the billing data for some key accounts and I was asked to build a summary sheet that brought all of them into one place so that the billing department didn't have to open each individual spreadsheet each month to verify their info to mine. What I would like to accomplish is to stay current according to today's date.
So if it's March, 10, 2014, I need to update the spreadsheet data according to the February 2014 spreadsheet a Key Account #1. Then in April, I need it to look for the new March 2014 spreadsheet. I'm assuming I can use an =IF(OR(... formula for this, but I'm just not sure how to get the date thing sorted out.
As far as naming of the the Key Accounts billing spreadsheets, they go something like 0214 "KeyAcct #1".xlsx. Then the new one will be 0314 "KeyAcct #1".xlsx. Even tho the next months billing file isn't created, the formula should be able to check against the date and know when to look for the new one.
I have a workbook. This workbook is divided into 4 worksheets. I want to add some of the calculations I made on other worksheets onto 1. I looked for the formula on how to do this, and this is the formula that was given:
=Sheet1!A1
I found the formula here: [URL] ........
Does it matter that the cell I'm trying to link from, in this case A1 on Sheet 1, is the result of a formula--a simple addition--and not a number, like 1?
I have a workbook with 2 worksheets. I know I can link 2 cells between worksheets, therefore if I change the contents in Cell A1 in Sheet 1, it will then show the updated value in Sheet 2 where I have entered that I want the value in Cell Sheet 2:Z1 is the same value as "Sheet 1! A1"
Is there any way to do this same process for an entire row? I would like to ensure that any change I make to a Row in Sheet 1, is automatically reflected in Sheet 2. I was hoping I could link the entire Row between the worksheets, not just the cells, which is more tedious.
The contents for each cell in the row varies: Text, Hyperlinks, etc.
Essentially what i am trying to do is link this to the other worksheet which breaks out if A is a Catalyst, Credit,Hedge, or Cash. If it is credit, then derive the the Value in the SUM Column for Credit.
TO explain better worksheet B looks like
A Credit
B HEdge
C Catalyst
The Final product what I am aiming to achieve is
Rows labels SUM Catalyst Credit Hedge Cash
A 10000 10000
B 7333.22 7333.22
C -60000 -60000
How I can link this without having to manually do it every time.
i am trying to get data from my original (vertical data) to copy to other group of cells (horizontal) without having to refer to every single cell to be copied. is there a way to get excel to do something like this: if b12=x then c12=next - but for 5 rows.
i am such a excel newb but i have asked some guys who say they know excel and i left them scratching their heads. i have all the data in i need in other sheets.
I am having trouble, with these formulas. I have 3 worksheets - Renewal, Upsell, and Crossell. Right now I have on Crossell the customer information cells = a designated cell on the Upsell sheet. This automatically polpulates the customer's info from the Upsell sheet to the Crossell sheet. What I want to do is have the customer's info populate to the "Crossell" sheet from BOTH the "Upsell" and "Renewal" sheet. That way, if you fill out the Upsell or the Renewal, it will always populate on the Crossell form.
I have created two workbooks, one which is a master price list (MasterPriceList.xls) and another which is a pricing sheet template that will be used to calculate many different products (Pricing.xls)
I have used a VLOOKUP across the workbooks (thanks to those who helped me with that) but I cannot get a drop list to work across two worksheets. When I enter the source in the "refers to" box, I get a notice saying that you can't use data validation across two workbooks. However, this was the same error message I got when I was incorrectly inserting the reference source for the VLOOKUP function, so I don't believe it Also, there are several tutorials on the web that say this can be done. However, none of them seem to work for me.
I cannot open the Data Validation box and get to the other open worksheet, so I can highlight the area I want, with the range I want to drop down. Until I close the data validation box, I cannot get out of that sheet. I have used cell ranges as well as named ranges.
When I type in a name, I get only the text I entered in the source reference box appearing on the sheet, i.e., the drop down box will only show "=C:Documents And SettingsAllenMy Documents, etc" it doesn't seem to recognize it as a source.
I have a folder in Dropbox (important that it is stored there, it is shared with 15 people who are on a bunch of different networks) that contains 15 spreadsheets. One of them is the master, containing performance data for 14 people. The other 14 are for each individual person pulling their performance data from the master. Monthly, the master spreadsheet gets updated, and I want the user spreadsheets to update, so I can email the individual spreadsheets to each person so they can track their performance. Of course, they cannot see each others numbers, so they have no access to the folder. There are a handful of people that have access to the master. All the spreadsheets will live in this folder all the time.
The issue is that on my computer the path to the master is different then it is for other users because of where Dropbox stores local data by default, and for a lot of reasons, I cannot change it.
Is there any way to tell excel that the master (or the other 14) live in the same folder so there is no need for the first part of the path?
The spreadsheet I have designed is to be input offshore by one group, and the values that get put in this shared workbook will feed into another shared workbook (for reference and ease, I will call one offshore. book and the other uk.book).
It seems that I have to save the offshore.book (so could I use some autosave code, for every 5mins or so? I dont have the plugin so would have to write it in), then hit update links in the uk.book (again, I presume I could write this so it triggers off every 30mins or so, but can be done manually if required). It seems abit fiddly doing this, but this is the way they wanted it done (my idea was to put into a database, but got out voted).
The figures in the uk.book would simply be a vlookup. However, we do not want the uk team to be able to look at the other book so would the links still work properly? (ie there would only be one open workbook at a time - direct access to each others spreadsheets shouldnt occur.)
I’m having problems linking data from various worksheets to one master worksheet, all in the same workbook. The workbook contains swimmers PB’s (personal best times) for each stroke and after each gala the swimmers new time is entered onto the worksheet. The worksheet data is then sorted so that the fastest swimmer is at the top.
The master worksheet contains all swimmers by name (alphabetically) and their best times for each stroke, what I am trying to do is have the master worksheet update automatically when I enter the swimmers new PB’s in the individual stroke worksheets, this works when I use ’Paste Special’, but when I go to sort the data with the fastest swimmer at the top, the data in the master worksheet does not reflect the correct times.
Using Excel 2003, I'm trying to come up with a formula that will take the students Total Pct. (column G, G12 is first cell & G41 is the last) from the worksheet called Juniors and find their Total grade (Column H, H12 is first & H41 is last) from the worksheet called Total Days. The % column on the Total Days worksheet is A (cells A10:A22) and the Grade column is B (cells B10:B22)
I have a number of separate worksheets in one spreadsheet all based on the same list (eg customers); the customer's name is the first column and hence the 'key' in each worksheet; when I insert or delete a line in the main worksheet the formulae in the first (customer name) column are amended in the other worksheets BUT I ideally need more than that; when I insert a new customer in the first (main worksheet) I need a new line with that customer name inserting into the corresponding place in the other worksheets; and when I move a line (eg delete a customer and move them to the bottom of the main worksheet list) I need the corresponding lines in the other worksheets moving as well. I guess what I really need is a drill-down function; a main customer list and sub-lists all linking back to the main lists like you would get in database tables.
I am linking a row of data in worksheet x to a column of data in worksheet Y and would like to be able to drag down the cell reference in worksheet Y in this way:
However, when I drag it down, the formula is automatically absolute for both the row and the column number instead of just the row as I indicated. Can anyone tell me why this occurs and whether there is a way to fix it?
