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?
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.
Each job has it's own xls file, this is a list of manually input costs and a total cost at the bottom (total is the same Cell reference: G24) for all xls files. The xls file is named the same as the job - F0001, F0002 etc.
At the end of every month we then want to create a master list xls file for all jobs being invoiced that month. This is a list of all the jobs - F0001, F0002 etc. In Column A, and the corresponding cost totals in column B.
In the master file we want to be able to type in the job reference to column A (i.e. the file names of the single job files -F0001, F0002 etc) and have the corresponding total for that reference display automatically in Column B (i.e. Cell G24 from file F0001 will display in the master file cell B1, when 'F0001' is typed in cell A1).
Ideally the master file would display all the individual entries automatically, so no need to input the job references either - simply scanning the directory for the other files present and creating a list automatically in the master file, alternatively it can get the job reference from Cell A1 in the individual job files, instead of from the file name.
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 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 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.
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.
I'm trying to create a VBA macro which will open external workbooks in a specific folder. thes workbooks will have filenames which are identical to the value in cell B1 of my active workbook. Once open, I would use indirect to pull certain results from that external workbook and then close it. I have a separate macro already which runs through the list. I found a few examples online but they don't seem to work.
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:
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 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.
Is there a way to create a pivot from multiple external worksheets. I have the same format and field names in different folders on our network. I am proabably looking at reading from 5 different files. Is it an option to write a query with UNION ALL?
I'm currently doing a survey using an excel workbook that contains multiple questions across multiple worksheets using radio buttons linked to certain cells.
I have around 400 workbooks coming back to me, so what i want to do is take specific values from across many worksheets within each workbook and combine them into a large master table in a seperate workbook.
I've tried using VBA, but not being very proficient at it i've hit a brick wall with that, so i'm hoping that there is an easier way to do it than what i'm currently pursuing.
I have three sheets "Contacts"/Sheet 1, "Judges"/Sheet 2, "Sponsors"/Sheet 3. Row 1 and 2 have been used as headers in each of them.
I would like to be able to enter a value in A3 of Sheet 1, such as 'judge' (a number would work fine too), and have the contact information in the same row (columns B-M) copy into the next available row of Sheet 2 in columns B-M.
I don't know if a formula exists for this but if I could type anything in the formula box and have Excel be smart enough to figure out what I'm asking I'd type something like this:
"Hey Excel. Follow this formula: IF 'judge' in sheet 1, A3; THEN copy sheet 1, B3-M3 to sheet 2, B-M next available row...Then do the same thing for 'contacts' but copy into sheet 3."
I have values on master sheet and want to link those values to source documents in the same workbook. But the values on master sheets are made up of more than one value on another sheet.
For example on master sheet I have $137,000 that is made up of values in P7 + Q7 + P57 + Q57 on source sheet. If user wants to see where this number is coming from; the user will click the number to go to source document.
On a single valve I linked to the source sheet to a single cell. One thing I was thinking, using conditional format, so if one of the cells is selected in source document the other cells that combine the total of the master sheet cells should be highlighted, just my 2 cents.