Finding Out What Links Are On A Worksheet
Mar 31, 2007
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?
View 9 Replies
ADVERTISEMENT
Apr 4, 2006
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
View Related
Oct 15, 2009
I have developed an application (all written in Excel) that can do comparisons between up to 5 scenarios (A.... E, with A being the Master that the others are compared to. Any one can be designated “A”.
All scenarios are the same format, and all have an identical Transfer Worksheet, to enable the transfer of data from the others to A for comparison to A. So, for A the transfer worksheet becomes an Importer, wherein the user pastes-special – link into array, always B21:E35, one column at a time, for each of up to 4 scenarios B…E.
For the others, Transfer worksheet becomes an Exporter, available for the user to copy the data from each of scenarios B….E, always cells B41:B54....
View 14 Replies
View Related
Apr 26, 2013
I have "inherited" an Excel workbook from someone that is really unwieldy in presentation and difficult to read. I want to break it down and reorder some of the tables and information. To do this I need to copy/ cut parts of different sheets into others. When I do this, I'm finding that I get reference errors even though the source documents are the same.
View 3 Replies
View Related
Jun 18, 2013
How can I break the link between a chart and a worksheet? If I change the name of the worksheet and try to use the chart I got an error that the current worksheet is the one that the chart is related to. I tried changing the source from the links option in the bar - no effect. I checked conditional formatting - also no effect. How can I break all existing links between the sheet and the macro?
View 2 Replies
View Related
Jun 27, 2008
[url]
I was wondering if it is possible to update links from one worksheet to another that contains a running tally - even when another worksheet is added and needs to be linked to the running tally as well?
I have attached a sample workbook. What I have is a summary sheet ( Sum Hrs CMS) that has SUMIF calculations for a series of rows in the Estimate Sheet.
The problem that I have run into is - when you press the Add New Sheet button on the Estimate sheet, it copies hidden sheet and inserts it at the end of the workbook, however I then need the hours in the CMS portion to link to the summary sheet. What I was wondering is how do the links on the 'Sum Hrs CMS' sheet include this new sheet and the data that is entered on it?
View 3 Replies
View Related
Jul 4, 2014
When i save the worksheet cells are loosing links to other cells. what is even more surprising it happens only for 6 cells in 6 sheets.
scheme of links
Basic data
P1
P2
P3
P4
P5
P6
On the sheets P1-P6, each cell A1 has a link to Basic data.
View 2 Replies
View Related
Sep 23, 2007
I am try to copy a worksheet "exactly" from one workbook to another, i.e. all formulas and range names reference the new workbook and not link back to to the workbook they were copied from. The code below brings the formulas and named ranges over still referencing the source workbook: SourceWB.Sheets(sht).Copy after:=TargetWB.Sheets(sht)
View 3 Replies
View Related
Feb 13, 2008
I have a template file which has a reference to a cell on a sheet in another workbook.
I need to copy this template to 250 workbooks. However, everytime I copy this template sheet into a workbook, it updates the reference to the template name!
Is there an absolute reference I can use for the sheetname?
Example: Workbook Template - Sheet1 - Cell A1 = Value
Workbook Template - Sheet2 - Cell A1 = Sheet1!$A$1
So for so good, but when I copy Sheet2 to a new workbook, I need to have the exact same reference; ie. Sheet1$A$1 and NOT = ['C:Workbook template']Sheet1!$a$1
View 3 Replies
View Related
Feb 15, 2013
Upon opening after "Enable" is selected the workbook attempts to locate several nonexistent pieces of data, either internet based files or network based files. Requested data appears to be about 11 years old and would not be applicable it located.
Edit Links shows the location of the requested files, i.e., E:filename but does not show the location within the document that causes this request. A search for "E:" does not locate text in any worksheets.
The question is how to delete or turn off this problem which slows opening, saving, and recalculation of a large multiple worksheet workbook.
View 1 Replies
View Related
Mar 16, 2007
how to protect a worksheet from editing and at the same time allow all data source links within the sheet to be refreshed? I need my users to be able to refresh all data links as required but not be able to change the worksheet in any other way.
View 2 Replies
View Related
Jun 22, 2012
I 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 Related
Dec 21, 2012
VB:
GetData FName(N), "Worksheet1", "H10:H10", destrangeName, False, False
is there a code i can use to choose the 1st worksheet insteading of selecting the worksheet1 in workbook? Because there are different file that got different worksheet name. E.G. worksheet1, worksheet2 or maybe Addidas, Nike.
View 3 Replies
View Related
Jul 29, 2013
I have three work sheets.
Worksheet A contains the Names that I want to find in Worksheet B and C. Trouble is, the format of the names are different in each Worksheet.
For example:
Worksheet A, Column 'Name':
Kyle Rowlie
Worksheet B, Column 'Name':
Kyle Rowlie, Head of Excel
Worksheet A, Column 'Name':
Rowlie, Kyle
What I need to do is create two columns in Worksheet A that identifies Kyle Rowlie is duplicated in Worksheet B and C.
View 5 Replies
View Related
Feb 9, 2012
I have a number of spreedsheets that may have a 1000 worksheets. Is there a way to find a particular worksheet using a search by the name?
View 9 Replies
View Related
Feb 14, 2014
I have my training file that is currently set up and working how I want it to, however I would like to go one step further if it's possible.
on the FIRE EXTINGUISHER TRAINING column there is one person out of date and 14 people that need the training, how can I populate all the name that need this training onto sheet 1 into the box that I have created. I'm wanting this to happen automatically so I don't have to retype the name onto this sheet.
View 14 Replies
View Related
Sep 27, 2009
Is there a simple way using VBA to find a date in the format of dd/mm/yyyy? I have a range on every sheet from A1 to IQ1 with dates already entered. I want to add this vba to some other code so the user can go straight to the date entered on the worksheet.
Using the edit find, it can find dates easily. Can this function be replicated through VBA?
View 9 Replies
View Related
Mar 25, 2014
I've defined a string array and would want to use it as a basis for a vlookup. Is it possible to find the elements of the array directly in the worksheet ?So far I've got :
[Code] .....
View 2 Replies
View Related
Mar 6, 2012
I have created a worksheet that uses lookup for the numbers that are in sequence but am unsure what formula to use when trying to lookup non sequence numbers as shown below.
Sequence No's
Cell A1:A10 would be 1,2,3,4,5,6,7,8,9,10 (as example)
Non sequence No's
Cell B1:B10 would be 2,5,1,7,3,8,4,10,6,9 (as example)
What do I use to lookup, if cell C1 = 1 and needs to find the No 3 from the Non sequence cells?
View 5 Replies
View Related
Aug 9, 2012
in finding duplication within different worksheet tables using excel VBA.
Sheet 1 has the following table below:
A
B
C
D
E
F
G
[Code]..
The format of both tables is different within both of the sheets.
The desired output should be shown in sheet 2 table:
A
B
C
D
E
F
G
[Code]..
The above table (sheet 2) shows duplicate rows (bold) from comparing table in sheet 1.
I was using this function below, however this does not give me the desired output i am looking for.
=IF(AND($F2=Sheet1!$F2,$H2=Sheet1!$H2,$E2=Sheet1!$E2,$G2=Sheet1!$G2),Sheet1!I1,"").
View 7 Replies
View Related
Feb 20, 2009
It might be a simple question but ive been trying for over a week now and i just cant do it (probably cos my knowledge of VB is useless )
What i need to do is:
I have 2 worksheets: worksheet 1 Articles and worksheet 2 New Prices.
Whats common on both worksheets is Reference Nº (In the case of worksheet 1 its column E and in worksheet 2 its B), what i need to do is replace the price corresponding to reference nº in worksheet 2 (In column E) with the price of the corresponding reference number in worksheet 1 (in column N).
Its a basic find and replace, but its a list of over 17.000 articles so the best way is VB, so i look through your lists and helps, but i just cant do it.
View 9 Replies
View Related
Sep 10, 2006
I cannot figure the formula for finding an intersection of two points on a seperate worksheet. I have attached the workbook.
View 6 Replies
View Related
Dec 11, 2007
I am writing a macro to track stats in a workbook. The workbook has the check every tab in the given workbook, hidden and unhidden. In order to track the stats I find a column labeled "Read Dates" on each worksheet. The "Read Dates" column is not always in the same location so I have the macro Find the words "Read Dates". To make sure the cell the macro finds is the column label and not just another cell where someone may have used the words "Read Dates", I have an 'If/Then' statement checking the column to the left to ensure "Rev Mo" is there. If the state is true, the macro begins tracking stats. If the statement is false it attempts to find the next cell containing "Read Dates".
My problem occurs when the macro selects a sheet that contains no data or does not have the words "Read Dates". To help remedy this, I have an Errhandler that simply tells the macro to move onto the next worksheet. My problem is that there are several worksheets that do not have a cell with the value of "Read Date" so the second time the ErrHandler errors. Below is the section of the code I currently have an issue with.
Sub Begin_tracking()
'Error Handler
On Error Goto ErrHandler
WCount = Worksheets.Count
For i = 1 To WCount
If Worksheets(WCount - i + 1).Visible Then
Worksheets(WCount - i + 1).Select
Can anyone think of a better way of doing this so the entire workbook is worked or tell me how I can reset the Error Handler is it will move onto the next worksheet? I've looked into help on this forum to rest the ErrHandler but what I've found and tried has not worked. The ErrHandler may be invoked 2 times or it may be invoked 20 times depending on the workbook.
View 8 Replies
View Related
Feb 25, 2014
I have been trying to write two formulas in one cell. I have been able to write them both separately but have been unable to join them both together. What I am trying to do if first search name them how many reoccurring numbers appear. I have provided an example below
a b c d
1 Tom 333
2 Sam 22
3 Sam 22
4 John 5
5 Sam 22
6 Sam 1
7 Tom 3
8 Tom 333
So the answer would be
Tom = 2
Sam = 2
John = 1
View 8 Replies
View Related
Dec 3, 2009
I have dates (12/3/09) in columns A, G, K and P.
There may or may not be a date in any of these cells.
How do I find the very last date in the worksheet.
I want to do this with code.
View 9 Replies
View Related
Jun 15, 2009
I've been having a small problem with my UDFs. I'm sorry if it's already been asked but english isn't my first language and I don't know the proper search key works to find my problem.
Basically, I've been programming functions destined for a single workbook so I don't use addins. If I write a function called J13F and I try to write that in one of my excel sheets, it generally doesn't work. Excel makes me write it like this:
='myfilename.xls!'J13F.J13F(Sheet!F21)
However, sometimes when I close then open my file, this won't work and excel will make me use my function normally: J13F(Sheet!F21)
I'd like to be able to use the function normally in every sheet in my workbook, how can I do that?
View 7 Replies
View Related
Feb 7, 2010
I am using several spreadsheets which are currently linked to specific cells in other workbooks. These are updated through dating the links whenever needed. However I would rather not use formulas in the cells at all and only have value updated by using the link function within VBA. I cannot find how to do this though.
Just to reiterate. I currently have cells that have"='\server01main_foldersubfolderDocument.xls" for example. The formula syntax is not exactly right but in the spreadsheets it is so you get the idea. These links are updated (when the workbooks are not open) when the links are update via the edit menu or a macro which does the same thing.
What I want to do is have no formula in the cells (only values) and use the feature that takes the value from the other workbooks but in VBA exclusively. This is what I have not been able to find out how to do.
View 5 Replies
View Related
Sep 9, 2009
I've created a spreadsheet which pulls data from about 5 other spreadsheets via links in the formula.
Unfortunately I am having trouble with these updating. I have to open the 5 individual spreadsheets in order for the data to pull across otherwise I get VALUE in every cell in the amalgamated spreadsheet.
Any thoughts why this should be the case? I've tried it in the past and it's always worked fine.
View 11 Replies
View Related
Feb 20, 2014
I just want to open some links in a new tab in a browser with a single click in a button.
For example:
[URL]
then in a new tab
[URL]
View 1 Replies
View Related
Apr 11, 2008
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 Related
