I have inherited a large spreadsheet. When it opens I get a warning message "This workbook contains one or more links that cannot be updated". When I click "Edit Links" the dialoge tells me that it is looking for a file in a folder called "All Daily Tasks". now I know that this folder no longer exists. I have found all the cells that had formulae refering to it, and pointed them in the new, correct folder, but I still get the warning message. I have cleared all Named Ranges, so that isn't the problem. And I have done a search for "All Daily Tasks", but this has come up blank. I know I can break the link, but I want to know where the link is, in case it needs updating. How do I find the cell with the "link that cannot be updated" if the search facility can't locate it?
So my macro goes through about 30 workbooks--opens them, updates the links, saves, closes. It would work flawlessly except I have to stop and click "continue" when this message pops up for most of the workbooks:
"This workbook contains one or more links that cannot be updated....etc."
About 90% of the workbooks have bad links somewhere (I think in the insert>names...but they're not my workbooks so i can't delete all the names). Since I can't find the bad links, I would like a visual basic script that will automatically click "continue" everytime so that the macro runs its course without me having to do anything. Does anyone know how to do this?
This file pulls totals from external links. The way I have it set up is that every month our log is saved to a naming scheme involving the date, then a fresh log is created for the current month. So, for each month I have "log mm-yyyy".
Now, the chart file is a thirteen month rolling chart (meaning every month it must show the thirteen most recent month's totals). I have created a macro to automatically update the links, based on an input in cell A:1 (meaning you can enter a date in the past and view the thirteen months previous). The problem is that we have only been doing the log for 10 months. So, when the macro runs to update the links, and it gets to months 11 through 13, it pops up a window for the user to manually browse for the missing(non-existent) files. There are quite a few cells that contain links to various information on these logs. I get a prompt for each cell and have to click "cancel". Once I have clicked cancel through all of the broken links, then everything works great.
My question (after all that) is, is there a way to suppress the prompt for the user to search for the missing file? Like I said, this chart is for management, and they shouldn't have to click cancel a dozen times just o look at a chart. If the file doesn't exist, then just break the link.
Obviously once the next three months are over, this problem will disappear, but in the mean time I need a solution.
I have a spreadsheet that has been sent to multiple clients. I have discovered an error in a few of the cells:
1. a link between two cells is broken so the correct number is not showing up in the cell. It is a simple formula ie: =A15, that I either forgot to put in or deleted in a fit of madness. It is in the same cell on the same worksheet in the workbook.
2. A hidden column on a worksheet has a formula that sums a range of cells. I have discovered that this range of cells is wrong. This error is duplicated on a number of worksheets in the workbook. The problem is that the error is in the same column in each worksheet but not in the same row.
Each worksheet is password protected. This is done through VBA code so the passwords won't be a problem.
The clients have already started working on the spreadsheet that I sent them and I want to send them a "fix" so that the correct formula will just update the spreadsheet they already have rather than me sending a completly new one and them having to start from scratch.
I had this wonderful spreadsheet set up with columns that appear and disappear and formulas galore so that the client didn't have to do to much work and then I find these errors after it has been sent. My testers let me down but that's life.
I'm making a macro to show or hide rows depending on whether or not a checkbox is checked. I'm using Wingdings font checkboxes, not actual form controls. The question: My macro can't find the last row of the used range on the sheet. This is in the sheet's code, set to fire when the selection changes.
Normally the line in blue works just fine, but it's not working here. In the attached workbook, the macro thinks the last used row is row 19, when it's actually row 21. What's going on?
In a worksheet, I have 15,000 links to data files as follows:
'C:DataA1[DataA1] 'C:DataA2[DataA2] and so on.....
I would like to replace them as follows:
'C:DataB1[DataB1] 'C:DataB2[DataB2] and so on ....
As you can see, I only want A to be changed to B, however, when I use Find & Replace, it will prompt me the location of the new link file for every single replacement.. 15,000 !.
I have a file contain some links with the other file. I want to remove these links but I don't know which cell contain the links. Is there anyway that can let me know which cell contains links so i needn't search one by one?
I have a document (unfortunately I cannot attach it) in Excel 365 that each time I open it I get the usual prompt that it is calling an external link. As much as i'd love to hide the popup I need to find the link to solve to problem but I'm at a loss.
Simply breaking the links doesn't work for some reason, so I've tried updating the source to reflect the current file. No luck there.
I've searched the workbook for the name of the link in question, searching for all formula for name itself as well as variants of "[" and ".xlms".
I don't see anything in the name manager referencing that external file.
I don't recall if I copied from that particular document, I may have as it was a duplicate file. I tried looking over the cells I believe I copied from it but didn't see the reference.
I've installed Kutools and Bill Manville (MS MVP) FindLink Tool. Both of which says there is no external link.
I've looked at the compatibility checker which basically just says, yes there is an external link but give no insight on how to address.
I've tried a couple of macros, some of which crashed excel, some of which simply said that there was an external link but not how to find it. In my frustration I forgot which one that was but I'm about to try that approach again and look for that code.
I've got an excel file with LOTS of links... it's a sort of 'dashboard' that draws in data from all the individual data files in the company.
Recently, when I open the file it's telling me "this file contains one or more links that cannot be updates".
So... as I usually would, I use my 'find links' add in to find anything with a #REF in it... nothing. Go into edit links and manually update each link... and they all update with no issue.
I have a file that's supposedly taking some of its values from another file. I say supposedly because when I follow the directory path, its not in there. I think I did find the file its referencing (same name, now in a subdirectory of the original reference). But I changed a value in the file being referenced and it didnt change anything in the active sheet.
I imagine the previous paragraph is a bit confusing so let me say it in another way.
I'm working on a file named A. In that it refers to a file named B, in my DocumentsFinancial directory.
In the DocumentsFinancial directory there is no file named B. However I did find a file named B in DocumentsFinancialWork.
I assume this is what A is referring to. But when I changed the relevant value in B (which does equal the value in A), the value in A didnt change.
I do remember in certain exceptions, Excel files have to be refreshed, I think when manual calculations are on. I'm not sure how to check for that, but I did go to the Data Tab and hit Refresh All.
Function MultLookup(Rng As Range, lookup_value As String, col_offset As Integer, Optional num_return As Integer) As Variant Dim myArray(), cnt, cell For Each cell In Rng If cell.Value = lookup_value Then cnt = cnt + 1 ReDim Preserve myArray(1 To cnt) myArray(cnt) = cell.Offset(0, col_offset).Value End If Next cell
If num_return = 0 Then For i = 1 To cnt If i = 1 Then MultLookup = myArray(i) Else MultLookup = MultLookup & ", " & myArray(i) End If Next i ElseIf num_return > 0 And num_return
I need to know what driver led a given lap of a given race (Col. G). Attached is my attempt. Something to consider is I need a formula that can be pasted down Column G as from track to track, from race to race the # of laps will not be the same. The number of drivers that led a given race will not be the same.
I have an array formula in cells of Column G for the # of laps in a given race (i.e. if a race was 100 laps it would go from G3-G100). I ran it down to 500 just to cover myself.
…it kinda works, but for some reason when it comes to lap 251 it gives me a #N/A error. It gives me the correct driver for lap 250 & 252 and all other laps but not lap 251. Also, a minor inconvenience is that I have to put a space after the first number & if at all possible I don’t want to have to do that.
I would love a macro where all I need to do is paste Column A, press a command button, & the macro does everything for me but that’s probably asking a lot. I know how to record a macro but don’t know anything about VB.
Column D & F have to be formatted as text. It has to be text format or when I cut Column B to Column D it changes some numbers to date (i.e. 1-2 becomes January 2nd).
how to create a column chart with a broken y-axis? And if this is possible, is it also possible to have 2 gaps in the y-axis. I have several countries in which i gathered information about their (expected) population sizes over time (2003 - 2011). For each country i want to make a chart in which their population size is shown over time. In order to compare these countries i want these charts all having the same y-axis scale (which expresses the population size). To be able to do this i want to put some gaps in the y-axis. I think i need 2 gaps because i can divide the countries in "low population" countries (2-3 million), "moderate population" countries (7-14 million) and "high population" countries (> 30 million). I attached a sample file to make things more clear! I hope somebody knows more how to do it!
I'm trying to create some graphs with broken lines from lookup formulae, essentially what is explained at http://www.ozgrid.com/Excel/broken-line.htm. My problem, however, is that my charts are rather more complicated than those outlined, having multiple series that move across the page and overlap a lot of the time. This prevents the suggestion there from being an elegant or even sufficient solution. Does anyone know of a better way of doing this that might work for my situation?
I have a set of sales data which shows the dates of transactions and also the product type that was sold. I want to see the monthly sales for each product type. I can get a total for all product types over the months using the following:
I have a workbook with 8 sheets, one of which is the "main" sheet that holds all of my user data for logins at my job (I'm in IT). There are about 4300 users and 23 fields for each user, so there are 4300 rows in the sheet. Other sheets in the book are important, but not as much as this one.
The workbook has a total of over 1000 lines of VBA code right now, as I'm building a tool that finds users, adds users, modifies users, etc. (I know, Access would be smarter for this, but for some reason they want me to use an xls). Most of this code works with the sheet that I'm having the problem on.
This ONE sheet has seemed to disable the functionality of the scroll wheel, and I can't use Page Up or Page Down while in the sheet either. Also, if I select cells with VBA (Sheet2.Range(<range>).Select), it will select the cell, but it will not draw focus to that cell on screen (the sheet will not move at all). The other 7 sheets in the workbook have all regular functionality.
This just started happening a couple days ago, and I've been building the code for about a week, so I'm wondering if it's something in the code...?
I have been using MSNStockQuotes on a few computers without any problems for months.
Now (in the last day or so) , when I press "update Quotes" on any of the computers, I get a "Failed to get requested Data" message. I can often "trick" it into getting the quotes by deleting a bunch of the quotes, then choosing Edit-Undo, which then fetches the quotes properly, but even that fails some of the time (same displayed message).
This happens even with older copies of my spreadsheet that used to work perfectly.
Are others having problems with MSNStockQuotes Excel Plugin?
Undo / Redo - they do NOT work properly after a save. Before a save, its great - I can undo / redo many many changes.
After a save, I can only undo 2 changes, but the worst thing is it ONLY REDOES ONE of them.
I work by saving very regularly, then I go and look at something on the sheet, and then use Undo / Redo to get back to where I was last updating the sheet and see what I had just been working on, especially if its a few minutes in between.
However, when I do that now, it undoes my last change, but the redo has been lost. So I do:
change, save, undo, redo
and it just doesn't do the redo. The redo button is greyed out.
If I do:
change, change, save, undo, undo, redo, redo
the first redo works, and the second fails as the redo has been lost.
It seems pretty basic that undo / redo should be symmetrical but they aren't, at least now.
I would rather just not have undo after save at all than this broken version.
Why are they putting so much stuff in Excel but they can't even get one of the most basic parts of spreadsheets correct - such as undo / redo, and the ability to open independent spreadsheets without having to change things in the registry (which I have had to do so I can work on two spreadsheets in a semi-sane fashion, undo being shared across your sheets otherwise). MS needs a back to basics...
I have everything else like it is supposed to be, but in one cell a word is broken. I have tried everything and gone back through the tutorials. Excel 2007.
I went to add the count to the inventory, it keeps returning an error where the date is pasted from one sheet to the other. I had originally tried to reference the value of the date directly, but because it was being passed to a merged cell, Excel didn't like it too much, so I had it copy and paste it. That worked at first, now it suddenly doesn't.
I have a column of over 500 replies to a survey. I want to create a pie chart showing the average ages of the respondee to that that question, broken down into increments of 5 years (this was the question -- how old are you) and reported as a percentage.
I have two worksheets, one with detail monthly information and one with YTD information. So let's say the three numbers I want to capture in the YTD sheet are in columns B, G and I on the monthly sheet. January's data might be in B5, G5 and I5. February's data is in B12, G12 and I12 and so on.
On the YTD sheet in cell C2 I link to Monthly!B5 and in C3 I link to Monthly!B12 so cells C2 to C13 on the YTD sheet show the monthly totals from column B on the detail sheet. On the YTD sheet, cells C20 to C31 show the monthly totals from column G on the monthly sheet, so cells G5, G12, etc. And finally, cells C40 to C51 on the YTD sheet show monthly totals form column I on the monthly sheet.
In the past I've always created all these links manually. After creating the links in C2 to C13 on the YTD sheet, is there a way to use a formula in C21 that uses the link in C2 to create a link for G5?
I am looking to create a function that outputs the upcoming quarter end date based on a specified start date, for which the quarter end is based on a broken fiscal year ending december 15.
As an example, say that you sign up as a customer with an internet provider on 2014-01-01. The internet provider charges all their clients on a quarterly basis and have a broken fiscal year ending on december 15. Hence, as you signed up on 2014-01-01 you will be charged on 2014-03-15, which is the date of the company's first quarter end.
So what I would like to do is to set up a function that outputs the first date I will be charged based on the date that I sign up. If I sign up between december 2013-12-16 and 2014-03-15, the formula should output 2014-03-15. If I sign up between 2014-03-16 and 2014-06-15, the formula should output 2014-06-15 etc. etc.
I have created a DDE link which is connected to Excel and refreshes on every change but the problem is it's only 1 row in height, so as new data comes in I lose the previous. Instead I want the old data to move down a row and I want this to continue as new data comes in.
I have a couple of workbooks, workbook1 pulls information from closed workbook2. The current link works fine, it returns the value that is in cell E10 from the worksheet 'Totals' from within the workbook 'Week 12 Yellow.xlsm'.
[Code]....
What I would like to do is make two parts of this link variable; Name (so 'Marc owens' in the above example) Worksheet (so 'Week 12 Yellow.xlsm in the above example)
As stated, the worksheet in the formula will be closed so I cannot use the 'Indirect' option. I have come across a lot of talk about the 'Concatenate' option but this this isn't working, don't know if its the way I have the formula or if I need something extra adding.
But this just returns "G:HelpdeskTotalsMarc Owens[Week 12 Yellow.xlsm]Totals!%E%10" in the cell the formula is in.
I've changed it so that it is
[Code] ......
But again I get "='G:HelpdeskTotalsMarc Owens[Week 12 Yellow.xlsm]Totals'!%E%10" returned which is the same formula as originally used (top of page) but it doesn't return a value.
I have created a data base of all the spare parts we have at work.
In the last column I have entered a hyperlinks that take you to a layout of the store and shows you where the part is located i.e. – 1C would take you to shelf ‘1’ level ‘C’ on the drawing in another tab in the same Excel workbook.
I would be pleased if anyone could help me by suggesting away that I can make the destination cell highlighted or more prominent once you have clicked on the link.
Also can I make the Find window automatically appear when the workbook is first opened?
If you are working in an excel spreadsheet that is linked to other excel files, when you double click on the cell that is linked to another workbook, it immediately opens the linked workbook if your settings are correct. How do you change your settings to enable this functionality?
