I've got a workbook in which there're many formulas linking to other workbooks. Coz these other workbooks are frequently updated, so the calculation (update) in my workbook is painfully slow and time consuming. I wanna improve this, maybe by VBA or other way instead of using link like this. Anyone can give me some hints or way how to improve this condition???
View 7 Replies
if it would be possible to update all the links in a workbook at once. For example, say I have a worksheet which is linking to a file named something like 4-6-08 (1) and 4-6-08 (2) and so on with the number in brackets being from 1-21. Now say I have the same thing for 4-7 and 4-8 and 4-9. Now I want to change the links to point to 4-10,11,12,13 instead of 6,7,8, and 9. But still there are 21 copies of each sheet. Is there any way to change all the sources at once so all the 4-6's could become 4-10's and 4-7's to 4-11's?
View 12 Replies View RelatedI have several Excel files saved on a network drive at work. We run cost studies and have numerous links mixed in with these files. My question is, when we change data in one sheet, it doesn't update the other sheet where we have it linked. When you click on the address for the link you get, (\fileshareN:Raetc...) If I remove the \fileshare and leave it as N: the links update. What am I doing wrong here? It automatically adds this \fileshare piece when I add the link. When I have removed it and close it, the extra information is added and I am back at square one.
View 2 Replies View Relatedif it was possible to write a macro that will update a link to a file that has the same file name but now lies in a different folder/directory path. I have about 50 spreadsheets that link to this file which has now been moved.
View 9 Replies View RelatedI have a workbook (we'll call it Mainbook)that has links to files on a shared drive (call these subbooks). For some reason, unless the "subbook" files are open, my links in "Mainbook" will not update.
View 9 Replies View RelatedI have a master price list that will have items added or removed from it occasionally. I have sixty workbooks that contain detailed data on mixes of multiple products from the master price list. All the corresponding prices throughout the sixty workbooks are linked to their specific slot on the master price list. The problem: if the sixty workbooks are closed and anyone adds a new row to the master price list, when I open any of the workbooks the values update but the referenced cell does not.
So if the price for Product 25 is located in cell K118, and two products are added in above K118, when I open any of my mix sheets that contain Product 25 the linked cell should be to K120. But it's not doing it that way. It is holding on to the referenced K118 cell and updating the value to the wrong price.
I've played around with the VLOOKUP function but haven't been able to make it work. Linking the cell as I have has worked great, if I could just get it to adjust the reference as new rows are added to the source workbook.
I've figured out that it will update the workbooks when I add a row if all that are required are open when the addition is made. But my boss will not open sixty workbooks every time a change needs to be made to the master price list, which he controls.
My links are not automatically updating on my worksheets. I was recently working on a sheet that linked to a different sheet in the same workbook. I deleted some cells by dragging other cells onto them, but when I went to the sheet that was linked to this sheet, the old information remained. When I clicked the cells that should have said "#Ref", they said =Sheet1!#REf but still had the old information showing until after I hit F9.
View 2 Replies View RelatedIn my worksheet i use a macro to produce my results which runs perfect.
Later i copied my range and pasted as picture link in another sheet.Since then the macro takes a long time to run. I suppose while running the macro xl continuously updates the picture slowing the macro.
i already set calculation as manual but no use.
I have a large workbook 45MB with 40 sheets each containing a section that links to the server to update itself when the workbook calculates. With that being said it's impossible to have the workbook on Automatic calculate because the update process can take about 7-10 minutes so its set to manual. However, I would like to prevent the links from updating (its one link that is used to update the file) by possibly a macro that runs when the workbook opens and prevent the update link from running when I press F9 to manually calculate.
View 2 Replies View RelatedI and my organization have many files created using Excel 2003. I have now upgraded to Excel 2007 and soon I discovered a huge problem.
- When opening a file using Excel 2007 that was created with Excel 2003 the links in that file is automatically updated even though I get the edit links dialog and choose not to update.
After searching for a solution on the Internet I realize that this is a problem that exist when Excel opens a file created in an earlier version. Microsoft has no solution.
I need to be in control of which links that are updated and not.
Going back to excel 2003 is not an option. Saving the source files in Excel 2007 is not an option.
I'm linking some data from 2 workbooks in the same folder (using excel 2013)
I have a main rota that works great, called "Main Rota.xlsm" I then have a separate basic file called Email Rota.xlsx without all of the bells and whistles of the Main Rota, purley made to email out to staff.
I store both files in the same folder "Site Rota" and it works fine.... until I rename the folder or move the folder to my laptop.
The link in the Email Rota looks like so to start (calling cell A3 from the Main Rota) ='[Main Rota.xlsm]Planning Rota'!A3
As I said it works fine until I move the folder onto my laptop the link then changes to
='D:DesktopSite Rota[Main Rota.xlsm]Planning Rota'!A3
And I have to keep updating the link, I have read online that it isn't supposed to work like this and its supposed to read the file "cleverly" in the same folder ?
I would ideally like to keep both files in the same folder and to update from visa versa without having to update the links if I move it from PC to laptop with I do daily .
Does anyone have any code to put in Workbook_Open module that can get rid of this message that says "The workbook contains automatic links ......." and default the selection to Yes when asked if I want to update all linked information?
I tried this but it doesnt work ...
Private Sub Workbook_Open ()
Application.DisplayAlerts = False
.........
End Sub
Currently I am using VBA Code below to Stop Update links prompts
Private Sub Workbook_Open()
Workbooks.Open "C:TempBook2.xls", UpdateLinks:=False
End Sub
Is there code I can use such This Workbook so that I can save this file as another name and it will work? Biz Auto Merged Post;Dear All, I have found VBA code that works.
Private Sub Workbook_Open()
Workbooks.Open ThisWorkbook.Name, UpdateLinks:=False
End Sub
I found a topic here that wanted to know how to remove links, I wanted the same thing.
I found an add-in that identified and could remove any links in the workbook depending on the response to the dialog box. So far, so good.
But it found a link that was located on another page, this link was linked to a third workbook. But what the link is doing or what its association is w/ the other workbook is a mystery. How do I see what this link's purpose/function is?
I have Windows XP Professional 2002
I have not worked with dates other than calculating ages. Simple things.
My lastest formula I need is as follows:
Effective Date for Benefits to Start for New Hires
Date of Hire examples: either the first of a month -- 06/01/07 OR
06/??/07; example any date of the month other than the first
Benefits are effective the first of the month following 90 day waiting period.
Example: 06/01/07 = 09/01/07
06/05/07 = 10/01/07
I have a sheet for tracking my poker playing online.
In column A I enter the start time as an Excel Dates & Times and in column B I enter the end time in the same format.
It could look something like this:
Start time End time
2007-01-03 18:20 2007-01-03 18:50
2007-01-03 18:30 2007-01-03 18:45
(Swedish date formatting)
This means I have been playing two tables simultaneously. Now I could get total time in "table hours" using SUM Formula:
=SUM(B:B)-SUM(A:A)
which in this case would result in 45 minutes.
But I also would like to calculate effective playing time so if I play 10 minutes on two tables simultaneously it will only count as 10 minutes and not 20 minutes.
I have a calculation I do that calculates a clients "effective interest rate" if they make extra payments towards principal.. Calculation works fine.. However, I am now trying to figure out how to amend that code if it's an interest only loan, anyone have any ideas?
Here is the effective rate calcs on a random normal amortization loan:
this is in B2, and answer is 7%
=RATE(B4*B5,-((B3+B7)/B6),B7)*12
B3 = Total*Interest 279017.8
B4 = #*Years*in*Loan 30
B5 = #*Payments*/*Year 12
B6 = Total*Payments 360
B7 = Beginning*Principal 200000
B8 - Ending*Balance 0
problem is when someone is on an interest only loan they pay more interest than a normal amortization because they are not reducing the principal in the first x number of years. So I need to compare the interest only effective rate to an interest only loan.
Here is the example I'm working on... A client's loan is the following:
Loan amount - 131,538
interest rate - 6.15
30 year amortization
10 years interest only
normal client would pay an interest only payment of 674.13, then after i/o period would go to 953.80 for last 20 years of the loan, and they'd pay about $178k in interest.. Now if that client pays an extra 1,000 per year, I can calculate the amount of interest they'd accrue, but have no clue how to back into the "effective interest rate", basically that says you are paying the same amount of interest as someone with a x.xx% interest only loan.
My problem is trying to identify the applicable rate in a range of dates that are not consistent in every case. I have a number of orders that span 4 years. The rate charged has changed over time and therefore I'm trying to find what the applicable rate would be for that time frame. For example one of my orders was created on 2/27/2012 with a specific item, then again the same item was ordered on 9/10/2013 and I need to find what the rate should have been for both of those orders during those rate periods. I've attached a sample sheet with the 2 tables I've been trying to assess. I've tried using mulitple IF and VLOOKUP formula's but it doesn't work they way I need it to due to the inconsistency in Table 2. H
View 2 Replies View RelatedI have an excel file with the ID, Name, workID and Creation date fields. Some people have several work ID's created in different times. How can I select row that contain work ID's that were created the latest. I may have several records per person, I need only the latest record. File contains about 3000 records, I can't go through all of them.
View 9 Replies View RelatedI have an unwanted link and I can't remove it. I have tried through the Edit Links dialog box, searched for [ in formulas throughout the workbook and gone searching manually for the reference but to no avail.
View 9 Replies View RelatedIs it possible to have a formula that will return the answer as the value.
For example if I have 15.75 in A1 and I use the formula =ROUND(A1,0), the solution is 16, but when you click on the cell it shows the formula and not the value.
I know about Paste Special, but was wondering if anything could be added to the formula to provide the solution.
Would this require a macro?
my questions below are more oriented to hows & whys, as opposed to asking for a fast solution to a pressing problem.
I'm just reviewing the basics of SumProduct to be sure that I understand what I'm really doing when I deploy the function. So, I have the following formula:
I'm looking for a VBA solution that will return a list of unqiue job numbers in B by date, the below is some sample data in Sheet 1
Sheet 2 is the result I'm looking for and usually there will be muliple job mumbers for the same date and in this case I'm fine to leave it as a blank cell in sheet 2 for that day.
Unable to use advanved filtering as it's a data table thats changing all the time. It's verly likely that the same job numbers will be in multiple days....
I'm looking for a VBA solution that will look at the machine name above in blue and insert in in column E
Machine 2's expected results have been filled in....
I have an excel sheet which has been exported from an 'Employee Attendance Software',
http://www.flickr.com/photos/34247990@N06/4299288997/
In the above screenshot, The employee name highlighted in Yellow should feauture in column A in the cells highlighted in Yellow.
Similarly, there are some other employees in the list for whom I have to populate the data in a similar fashion
http://www.flickr.com/photos/34247990@N06/4299288959/
I am trying to solve a problem with = LOOKUP. Here's the data. A1 can have a value between 1-100.
IF A1 IS <80.00, NO MULTIPLICATION IS NEEDED
IF AI IS 80.01 TO 85.00, MULTIPLY A2 BY 0.37%
IF AI IS 85.01 TO 90.00, MULTIPLY A2 BY 0.52%
IF AI IS 95.01 TO 95.00, MULTIPLY A2 BY 0.87%
IF AI IS 95.01 TO 100.00, MULTIPLY A2 BY 1.07%
Is there a =LOOKUP formula that can be used here?
I have the new version of excel and I can't figure the correct formula to do the following.
Attached is my example.
A1 is available Inventory qty. C1:H1 are forcast qty's. I would like to have the available forcast qty. shown in black and the unavailable forcast qty. shown in red based on available inventory shown in A1
Example
So C1,D1 and E1 would be Black and F1 and G1 would be red. As inventory changes so would the colored numbers on the forcast.
I am using HLOOKUP to show a date depending on the month shown in B10.
If B10 is January then the dates throughout January ie. 01/01/08 to 31/01/08 are shown in the cell range of F13 to F43. There is no problem with this unless there are 30 or 29 days etc in a month. So if November is in B10, in F43 I have the value of 00/01/1900. Can I modify my formula so if there is no data in the lookup table to return a blank value ie. "".
Here is my formula:
=HLOOKUP($B$10,DATES!$B$1:$M$32,2,FALSE)
I obviously change the 2 to 3, 3 to 4 as I copy the formula down the table.
I would like to have the answer to a formula, say in cell G6, to show up in say G5. I notice in my college coarses, doing our homework through excel, the solutions are always in a cell around the entry cell.
View 9 Replies View RelatedI am currently using a macro which highlights keywords and associated page views from an array in my website stats spreadsheet. Now I need to take it to the next level:
I want to use a multi-dimensional array to pull out any keywords that are duplicated, and display them at the top of my spreadsheet with the totaled page views next to them in a different color (red).
Here is my existing Keyword highlighting
Sub Highlight_Keywords()
Dim vntWords As Variant
Dim lngIndex As Long
Dim rngFind As Range
Dim strFirstAddress As String
Dim lngPos As Long
