How can I detect on an excel workbook which cell have links(formula =) to
other workbooks without have to put my cursor on top of each linked cell.
I have had a hard time finding this cells. I can see the links by choosing Edit
then links, and excel will display list of links, but it does not specify
which cells contain those links
I have a workbook with a few worksheets. In sheet1 I have a few ranges which are linked to different parts in the other excel sheets.
Is there anyway to check if a range is being used as a link in another sheet.
Eg.
In sheet1 , A1 i have a value Apple.
In sheet2, A1 I have linked it to sheet1 A1.
is it possible to detect the link in sheet2 A1 from Sheet1 A1? I tried using Trace dependents but all it shows me is the link symbol? An arrow with a small excel sheet.
I often have Excel sheets in which Excel tells me there are hyperlinks and content from other sources. I guess it happens because I copy something from one sheet to another.
First, is it possible to copy-and-paste from a sheet to another and only copy the concrete formulas and formats, and no source to the old file?
Secondly, how can I detect where these hyperlinks and links to other sources are? I see they exists, but I can't seem to find them. Does Excel have such function? Can I detect them with a VBA script?
I'm working on a timetable for my boss and i'm trying figure out how to have vba detect when a specific set of cells gets an x typed into it and calculate 1 cell = 36.50 hours and input into the appropriate Sum column. The Sum Total is Column AR and i've put x's into some of the cells that will be containing them, i'm sure if i just get the basic code needed i will be able to implement it.
View 5 Replies View RelatedWhat I'm trying to do is link a cell to give me the sum of 2 different cells located in 2 different tables I created.
So I want to have Cell B5 look for the number in cell B3 in the Table that starts in column Q1 and give me the corresponding number in the same row in the column R1
then lookup the number in cell B1 in the Table that starts in column T1 and match it with the number in Column U1 that corresponds to it in the same row
and Finally give me the sum of both corresponding numbers.
IE.. the number in B3 is found in Q13 which uses the number it corresponds to in R13. The takes B1 finds it in T16 and uses the number it corresponds to in U16.
Then returns the sum of R13 and X16 into B5. (B5 should show 176)
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 RelatedI'm going to be using a spreadsheet to keep track of where different people are at. So if Person 1 is in Room 3, I will stick a 3 in the box next to their name and then can look at the spreadsheet whenever I need and see what room they are in. When I'm deciding what room to put a person in, though, I need to be able to quickly glance at a list of Room #'s and see what one's are still available. So I have a bank of Room #'s in the spreadsheet....1,2,3, etc.
What I'd like, is some way to set this up so that when I put, for example, "3" in the cell next to "Person 1" the spreadsheet automatically removes "3" from the bank of available Room #'s and when I delete the "3" because the person has left, it adds "3" back to the bank of available Rooms.
I would like to be able to find the largest visible area of continuous rows in a filtered table. I know one possible way would be to loop through visible cells using the "xlCellTypeVisible" property and count cells in each visible area. However, the data is consisted of tens and sometimes hundreds of thousands of rows so I was wondering if there is a faster, more efficient way to do this.
View 3 Replies View RelatedI am trying to use Excel to document fiber optic cables that run between different locations. Each cable has 2 ends, but whatever service is connected to one end, will also be a the other. I would like to use an Excel worksheet to represent a termination point (a "light shelf" comprised of several fiber optic cables each made up of several strands of fiber (kind of a table with the 1st 4 columns of 6 rows being cable A, and the next 8 cable B)). I would like to be able to type in the value for cable A Strand 1 and have it linked to another worksheet representing a different location (the other end) and vice versa. I have tried to paste a link, but that seems to get broken. Is there a better way to make permanent links (Excel is bawking about a circular reference, which it is as each end needs to be linked back to the other.
View 9 Replies View RelatedIn spreadsheet 1 I have a bunch of data, no formulas or anything, but data I need. I created spreadsheet 2 to take parts of S1 and have set links to cells in S1 so that when the data there is changed S2 gets updated automatically for me.
My problem is that when I add or delete rows or columns to S1 it's messing up my links in S2, not showing the right data. Issue with my linking that will tie the cells together when rows or columns are added or deleted.
I have cells (range named Formulas) in a worksheet A (wsA) linking to another workbook. This workbook has several sheets, each a different forecast. From a Radio Button Group on wsA I would like to choose forecast, FC1-FC3, and then all the cells in the range Formulas would be updated with the choosen FC.
I would like a VBA script to handle this:When the workbook is activated the present Forecast in the Radio Button Group should be stored as a variable (oldFC)When I choose a new forecast, the value should be stored in a new variable (newFC)I then want to loop through the whole range (Formulas) and update each cell and it's part of string value to the newFC
Before update:
wsA cell N3='H:[5500-plan-06.xls]FC 3'!N$16
wsA cell N4='H:[5500-plan-06.xls]FC 3'!N$17
When new Forecast is choosen this should be update to:
wsA cell N3='H:[5500-plan-06.xls]FC 1'!N$16
wsA cell N4='H:[5500-plan-06.xls]FC 1'!N$17
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.
I have a list of links in 1 excel worksheet. They are spaced every other row if it makes a difference. I need to create a macro that clicks on every link automatically. Each link is a direct file that I need downloaded every few months. I have no Macro experience beyond hitting record to automate basic tasks.
View 13 Replies View RelatedI would like to refresh all external links to other xl files (on a network drive) and break all links
I have a code like this:
Code:
For Each l In ActiveWorkbook.LinkSources
Workbooks.Open Filename:=l, updatelinks:=1, ReadOnly:=True
ActiveWorkbook.Close False
Next l
For Each l In ActiveWorkbook.LinkSources
ActiveWorkbook.BreakLink l, xlLinkTypeExcelLinks
Next l
It seems to be working, but if i go step by step (f8) it updates values but sometimes after closing the source workbook and updating next one, the previously updated values disappear...
I cannot open all files because there are many of them and they are like 40mb each.
There are no defined names in the file, except print_area
I have the following code in Sheet1 for file Book7.xls
Private Sub Worksheet_Calculate()
On Error Resume Next
For Each rcell In Range("A1:D6")
Select Case rcell.Value
Case Is >= 5
rcell.Interior.Color = vbBlue
Case Is < 5
rcell.Interior.Color = vbRed
End Select
Next rcell
End Sub
Range A1:D6 is linked to an external source file which I am changing every now and then.
A1 has the formula:
=[Book8.xls]Sheet1!A1*1
Copied all the way to D6.
[Book8.xls]Sheet1!A1:D6 presently has all values 1 hence the formatting in Book7 is all red.
Now I an changing source file to Book9.xls (thru EDIT menu->LINKS). [Book9.xls]Sheet1!A1:D6 has all values 10. But when I change external source to Book9.xls, the right formatting did not apply. All are still red (instead of blue). I still need to go in one of the cell and press F2 and F9 to trigger calculation.
How do I change the code in order to recognise the calculation in order to trigger the codes in the event?
I have a sheet called "Sheet1" in workbook "File1". In this sheet, I have a lot of formulas and some of them involves linking to external files located in E: Finance folder. Is there a way to automatically highlight those cells containing a formula that references to external files?
View 3 Replies View RelatedI need to send a file to hundreds of users so that I can tell who has a particular add-in installed. This file will then be saved to a particular directory. The problem I'm having is controlling the error that occurs when the add-in is NOT installed. The below approach (using On Error Goto) clearly isn't the way to go. I need something like
If(Iserror(AddIns("AmartaSpecs") Then Cells(2,3)="No"
The code I have so far is:
Sub Amarta_Specs()
Dim a
'ActiveSheet.Shapes("Button 1").Delete
Cells(1, 1) = "Username"
Cells(1, 2) = "Computer Name"
Cells(1, 3) = "Add-in installed?"
Cells(2, 1) = Environ("Username")
Cells(2, 2) = Environ("Computername")
On Error GoTo Error
Set a = AddIns("AmartaSpecs")
Cells(2, 3) = "Yes"
Error:
Cells(2, 3) = "No"
ChDir "E:FinanceSQL Add-in test"
ActiveWorkbook.SaveAs Filename:="E:FinanceSQL Add-in test" & Environ("Username") & ".xls"
End Sub
I am trying to create a code in the Worksheet_change event. I have hundreds of rows of data in a sheet. Each row of data is one unique project. What I am trying to do is figure out if any cell in a specific row has changed, and place a date at the end of that row idicating the date of the change.
View 9 Replies View RelatedHow to detect msgbox pup up event.
Does any function like msgbox.activate=true or somrthing can used.
I want to avoid msgbox show up and close msgbox automaticly.
I know
application.displayalters=False
But it's seem not to apply to msgbox close.
I searched this site and found the code below to check if a worksheet to be updated is already open. It works fine (gets the desired message) if the workbook is open, but if not open I get "Run-time Error 9, Subscript out of range". I've played with it for a couple hours,
VB:
Dim wBook As Workbook
'Check that Summary worksheet is available for update.
Set wBook = Workbooks("CommittedSummary.xls")
If wBook Is Nothing Then 'Not open
'Do nothing
Else 'It is open
i = MsgBox("The 'CommittedSummary.xls' workbook is already in use and
cannot be updated. Please try again when the workbook Is available", _
vbCritical, "Committed Request Edit")
Goto EndUpdate
End If
I have a column with employee id's. Sometimes two or more employee ID's are double. I need to dedect if there are ID numbers that appears more then ones. If I found such a number I can copy that.
Has someone an idea or is there a build in function to handle this?
I am doing a bit of data spring cleaning... I need some VBA that will look at column FT for duplicates. I want it then to keep only the most recent one, so it would delete the others rows completely. Column B contains a date and time. When removing the items it should make a copy of them to the sheet Delete Log.
View 1 Replies View RelatedIs there anyway to detect if the user has just printed. I have code which transfer data from the sheet to an access file when the user prints using the button I placed on the sheet, unfortunately some users are using the print button on the toolbar.
View 9 Replies View RelatedIs there some way to detect that a key (specific or any) is being pressed when an Excel workbook is being opened to activate optional precessing in the macro code?
View 9 Replies View Relatedif i have data across multiple rows than is it possible to analyze that data in say row 1 and once row 1 ends move to row 2. example
Array Data
Colmn A B C D E
Row1: 3 1 4 6 8
Row2: 6 7 8 9 10
Row3: 4 5 1 2 8
Row4: 6 4 3 2 1
Row5: 2 4 1 2 2
OutPut Format
Colmn F
Row 1: Age is 3
Row 2: Always Same - Note this appears even though not part of data
Row 3: Year is 1
Row 4: Time is 4
Row 5: Hour is 6
Row 6: Minute is 8
Row 7: !
!Not Part of Output Comment: Now row 1 data has ended. Move to Row 2!
Row 8: Age is 6
Row 9: Always Same - Note this appears even though not part of data
Row 10: Year is 7
Row 11: Time is 8
Row 12: Hour is 9
Row 13: Minute is 10
!
etc
etc
etc
also note that the text that comes up in each row is not part of the data portion. Its just the number or string that gets pulled from the data portion.
I'm trying to automate a spreadsheet that downloads a set of tickers from Bloomberg and then fills across a range with a series of data. I have most of the VBA code I need, but my problem is that on opening the spreadsheet, the code moves on to fill the range before all the tickers have downloaded correctly.
I've tried using application.wait and setting up loops until the time reaches a certain value, but these seem to stop Excel updating the list of tickers (presumably the loops stop excel gathering the data?). I suspect that what I need to use is something like the querytable. afterrefresh, but am not sure I'm using it correctly since my knowledge of VBA is very rudimentary.
(i) is there a way of getting the sheet to open, and then just wait for a period of time while still allowing the tickers to download, and/or
(ii) any way to detect when the tickers have finished downloading before the next step of code can commence?
How can I with vba detect when someone changes the color of the font in a cell
View 5 Replies View RelatedI am trying to detect if a user leaves a cell empty. I have tried If statements like:
If Target.Value = "" Then
and
If (IsNull(Target.Value) Then
The problem is when the user uses the spacebar to delete an entry in that cell. If they leave any spaces after exiting the cell those if statements fail to run.
In column A i have a range of dates
In column B i have a range of descriptions
In column C i have formula which calculates an interest figure based on the date entered in column A and a date entered in a cell elsewhere. It also makes the cell display a couple of different strings of text if conditions are not met e.g. It displays "too early" if the date entered is more than 4 years before the date entered in the cell elsewhere
The problem that im having is this:
When i try to calculate column C i want it to throw an error if there is anything but a numeric value in the column. But it is obviously considering the cells in column C with for e.g. "too early" as a "0". I have tried all sorts of formula to do this to no avail, IFTEXT, IFNUMBER, ISTEXT, ISNONTEXT etc etc.
how can I detect the OS of the computer a sheet is running on and launch a macro accordingly?
I have built a calculator that utilizes an embedded wave file but doesn't work correctly with Vista. With Vista it actually causes WMP to be launched instead of just playing the file. So I was hoping to be able to skip the sound on Vista machines but play for all other OS's.