Broken Link VB Workaround
Oct 9, 2009
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?
View 4 Replies
ADVERTISEMENT
May 10, 2013
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?
View 6 Replies
View Related
Aug 7, 2009
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.
View 6 Replies
View Related
Aug 6, 2007
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.
View 9 Replies
View Related
Dec 10, 2007
I am wanting to base some calculations on a filtered list, specifically visible cells. Subtotal works great on average and other calcs, but not median. Any way to get a median calc on visible cells?
I can't use sumproduct or ctrl-shift arrays because I don't know of a way to identify if the cell is visible (or hidden).
View 7 Replies
View Related
Sep 10, 2008
How do I avoid using arrays? I am reaching Excel 2003's limit.
Our workbook has many worksheets, each with tables generated from queries that have multiple columns. On the first sheet we have arrays that refer to the other worksheets. Such as:
{=SUM(IF(('Prime Change Orders'!G$2:G$10=C17)*('Prime Change Orders'!D$2:D$10
View 9 Replies
View Related
May 1, 2007
I have a dateset that I am creating a userform for. The problem is my dataset has more than 32765 rows, usually around 35000. How can I work around getting the "overflow" error when my users click on a selection > the limit imposed on a listbox?
View 3 Replies
View Related
Apr 1, 2009
I have some formulas in my workbook that use the weeknum function from the analysis toolpak. I have the analysis toolpak installed, yet every time I save the file and reopen it, the formulas with weeknum are gone, with #N/A in its place as the formula. Sometimes the results of the formula from the last time remain (the cells contain the number of the week, but without the underlying formula), sometimes not. All the other formulas are fine. The formulas are simple, like =WEEKNUM(C2)
I have been trying to save as a 97-2003 .xls file. Today I'm trying to save as .xlsx, but I have to rebuild the formulas first, so I don't know how that's going to turn out.
View 5 Replies
View Related
Mar 20, 2008
I am aware that special characters such as -,(,),[,] are not permitted in named ranges (for lists), but is there a workaround for this? I have a lot of dependent lists that need to access names that contain either a - or () or both. I could simply remove the -'s or ()'s using find/replace, but they are required, as they're part of the name. I am aware of this method http://www.contextures.com/xlDataVal02.html#Illegal but I have a lot of dependent lists across a sheet, and a VLOOKUP isn't going to work. If there isn't a workaround, are there any allowed characters I could use to replace the -'s and ()'s?
View 3 Replies
View Related
Mar 4, 2008
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 just cannot find anything that's broken!
View 9 Replies
View Related
Jun 12, 2013
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.
View 5 Replies
View Related
Mar 13, 2009
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
View 9 Replies
View Related
Nov 22, 2009
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.
Column G: { =INDEX($E$3:$E$500, MATCH($F3,VALUE(LEFT($D$3:$D$500,FIND(" ",$D$3:$D$500)-1)),1))}
…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).
Column B:
=RIGHT(A3,LEN(A3)-FIND("*",SUBSTITUTE(A3," ","*",LEN(A3)-LEN(SUBSTITUTE(A3," ","")))))
Column C:
=LEFT(A3,(LEN(A3)-LEN((RIGHT(A3,LEN(A3)-FIND("*",SUBSTITUTE(A3," ","*",LEN(A3)-LEN(SUBSTITUTE(A3," ",""))))))))-1)[/size]
View 8 Replies
View Related
Sep 26, 2006
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!
View 2 Replies
View Related
Apr 5, 2007
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?
View 9 Replies
View Related
Oct 15, 2008
I am in the process of reformatting an excel workbook to act as a review tool for different factors in a process. Part of my redesign includes the use of coding that creates different cell colors based upon the cell contents. The new workbooks will be used to handle existing data for this year. I have developed a process macro to open an existing workbook and copy and transfer the original data worksheet into the newly formatted workbook. The data gets transferred to the new worksheet and the resulting workbook is renamed and saved, Heres'' the rub... the newly saved workbook is missing all of the coding for the worksheets... apparently this is a MS bug.
Has anyone figured a workaround for this. One thought I had is to open both( new and old )workbooks and rather than move/copy , i would transfer the data using cell references.
View 5 Replies
View Related
Apr 27, 2009
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:
=COUNTIF(Licences!E2:E9999, "<39600")-COUNTIF(Licences!E2:E9999,"<39569")
36900 = Jun-08
39569 = May-08
But I need it to also break it down for product a, b, c so I need something else to add to the formula?
View 6 Replies
View Related
Oct 22, 2008
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...?
Does anyone have any idea what might cause this?
View 14 Replies
View Related
Jul 24, 2009
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?
View 2 Replies
View Related
Apr 14, 2006
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?
View 13 Replies
View Related
Jan 13, 2014
I've created a spreadsheet it is for tracking attendance etc broken down into percentages.
What I'm looking to do is if someone doesn't attend this doesn't get added to the attendance total.
I've attached a screen shot so you can see what I mean.
excel.PNG‎
View 7 Replies
View Related
Jun 8, 2010
I have Excel 2007.
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...
View 5 Replies
View Related
Dec 31, 2012
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.
View 6 Replies
View Related
Nov 19, 2009
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.
View 2 Replies
View Related
Sep 12, 2008
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.
View 9 Replies
View Related
May 24, 2007
creating breaks in the line this script now simply turns the line black.
View 9 Replies
View Related
Jun 28, 2013
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?
View 6 Replies
View Related
Jan 20, 2014
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.
View 1 Replies
View Related
May 6, 2009
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.
View 9 Replies
View Related
Mar 26, 2014
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.
Cell A1 = name variable & Cell A2 = worksheet variable
[Code] ......
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.
View 9 Replies
View Related
