Project is shared, cannot use vba.
I have in A2 - A12, The names of different sheets in the workbook. I would like to create a link so when a user clicks on the cell, it will take them to the specified sheet.
Lets say cell A2 contains the text '1208', when pressed I would like it to open up the sheet 'A1208'
I've created a chart in sheet 1 in a workbook. I want to copy and paste that chart into another sheet (lets take sheet 2) of the same workbook. I am using Excel 2010 version.
Whenever, I try to copy a graph and want to do "Paste Special as Link picture". The problem I am facing as "Paste Link" option is inactive.
I am attaching the Excel for your reference.
So I have a set of data in my sheet 2 that I want to be connected to sheet 1. The data in sheet 2 looks like this,
NO
Reaction
H
S
1
2Fe2O3 + 3C = 4Fe + 3CO2
105690
120.21
2
Cr2O3 + C = 2Cr + 1/2CO2
218,085
59.24
while in sheet 1,Reaction No
Carbothermic Rxn
H
S
What I want to do is that when I put a reaction no (e.g 1) at the empty cell next to the label Reaction No, the data in sheet2 (i.e. Reaction, H and S) would automatically be reflected in the empty cells next to Carbothermic Rxn, H and S.
I would like to run a macro from a worksheet that creates a new workbook for each worksheet (there will be 20 plus) and saves it in a new folder i.e. all the same directory L/Utilisation/Team/name of the worksheet.
View 2 Replies View RelatedI have a My.xlsx file that has data for single month and I want to make a single file in which I can have contents for whole year by copying this file 11 more times in the same excel work book. Finally I would have 12 sheet in a single workbook.
E.g. Jan-14, Feb-14, .... ,Dec-14
I don't need any calender data just 11 more copies of my original sheet in a single work book.
Creating a macro to compare 2 sheets in a workbook and print the differences to a 3rd sheet.
Each sheet will have the same number of fields, 5 columns with the header in the first row.
All values in the cells are integer except for the last field which will be a character.
The key is the value in the 2nd column. If it's not in the other sheet, then it's a new record. If it's a new record then highlight it a color depending on what sheet contains the new record. Now if the key is the same in both sheets, then check the other columns to see what's different. If there is a difference, print the record for both sheets in the third sheet and highlight the differences. I attached a sample of what I want.
I have some code that looks at the part # in column D and then goes out to the D drive to drawings (.pdf files) and creates a hyperlink.
The problem is that it is only creating a link to the .pdf file and not to folders. I need a hyperlink to the folder so when it is clicked the open folder pops up and shows the multiple files associated with that part.
Link should be the same regardless of file or folder, no? As long as the name matches link should be made, correct?
E.g.
123-100.pdf (column D part #) links to 123-100.pdf file on CD
456-200 (folder name in column D) no link created to folder named 456-200 on CD.
Sub Hyper()
MyPath = "D:"
StartRow = 2
EndRow = 20
x = 0
For i = StartRow To EndRow
Cells(i, 4).Select
MyFileName = ""
MyFileName = Dir(MyPath & Cells(i, 4).Text, vbNormal)
If MyFileName "" Then
x = x + 1
Cells(i, 4).Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="D:" & MyFileName
End If
Next i
End Sub
I am not sure if this is possible. I wish to have a chart in a worksheet linked to a stock chart on a webpage.
For example, consider this chart on Yahoo Finance. At present every week, I have to go to the webpage and copy the chart and then paste it into a worksheet.
Is there a way I can automate this task by linking the chart to an Excel object or by any other way?
Trying to create a new workbook from another open workbook, then copying all the sheets that aren't called "Summary" to that new open workbook and then saving it. I get a subscript error on this line:
[Code]....
I have some experience with excel, but until now have not ventured into VBA and macros.
I have a workbook which will have the following sheets:
1.Absence Summary sheet - Summarises data from each employee's individual sheet.
2. Template Sheet - A sheet formatted as an absence record sheet, but without data.
3. Individual employee Absence record sheets - Based on the Template sheet.
I have read with interest the various posts and help files on User Forms & Macros, but have got a bit stuck.
My Aim: ....
I have a macro that works across 2 workbooks. In the middle of the macro, a message box pops up "This workbook contains links to other data sources" and I need to select yes or no. This is because I am using a vlookup. Is there a code that I can put in to the macro to select Yes to this box and therefore not stop the macro half way through??
View 5 Replies View RelatedI have worksheet1 with 200+ customers and 12 months of data for each customer. I want to link each month to a separate worksheet (worksheet2, 3....12) for all customers. (i.e., customer 1's January activity across row 1, customers 2's January activity across row 2.....)
Is there a way for me to create a formula or copy comand to link row1, worksheet1's data to row1 worksheet2 and then every 13th row in worksheet1 to row2, row3...in worksheet2?
Right now I'm just putting the = in worksheet2 and mousing over to worksheet1.
I have an overview worksheet that features a list of dates; I want to hyperlink the dates to the another worksheet within the workbook that represents the month. I want to do this without changing the dates in the overview sheet. For example, I have 2014-05-23, 2014-05-24,2014-05-25 in my overview sheet and I have a worksheet titled May 2014. When I try to hyperlink the dates to the May worksheet it turns the dates to #### in the overview sheet. How can I stop that from happening?
View 13 Replies View RelatedI 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.
external links.png
How do I get rid of the link??? My formula seems like it is not working because it has a link which I cannot find anywhere.
dog.xlsx
Screen shot 2014-01-05 at 2.38.08 AM.jpg
I have a master for each manager that pulls a few values from each of their employees personal sheets.
The personal sheets (about 30) are individually password protected so only the owners can open them.
This is creating a security issues as the links in the master require the passwords for each of the "source" workbooks.
Is there any way to add some code so that the passwords do not need to be entered each time?
I inherited a spreadsheet that contains a link to another spreadsheet. I want to remove the link and I can not find a way to do this. I have checked under Edit/Links but there is not a delete or break links options. The linked spreadsheet does not exist.
View 4 Replies View RelatedI want to reference a cell from another workbook that is a hyperlink, but I want it to stay as a hyperlink and not text. Example would be workbook1 cell A1= workbook2 cell A5(which is a hyperlink) but in workbook1 A1 shows the text of the name of the hyperlink. I want the workbook1 cell to be exactly the same hyperlink...this way when I have multiple workbooks I just need to change the master workbook.
View 7 Replies View RelatedIm looking to insert a formula in vba code that inserts the formula from a closed workbook to the active cell. At the moment i have this as an example of what i need:
VB:
ActiveCell.FormulaR1C1 = "='[SRS 5FFBEP.xlsm]7%'!RC"
However the file part in square brackets [SRS 5FFBEP.xlsm] and tab 7% i would like to replace with a link to a cell reference that contains the file path as this needs to change and update so i would end up something like this:
VB:
ActiveCell.FormulaR1C1 = "=('" & Workbooks(Worksheets("SRS Files").Range("d29")).Worksheets("SRS Import").Range("c10") & "'!RC)
Note that cell Worksheets("SRS Files").Range("d29") contains the full file path (in text format) and Worksheets("SRS Import").Range("c10") is the link to the tab that reads the 7% (in text format).
As it stands the first set of code works fine however i need it to be able to change as previously stated. So what i need is to replace the file name and tab with cell references
Is there a way to specify the filename for an external workbook that can be referenced throughout the spreadsheet? In my case it would be better if I could change the single reference instead of having to change it throughout the entire spreadsheet everytime I needed to use data from a different file.
View 2 Replies View RelatedI have created a Vacation Calendar workbook with 6 sheets. All the sheets contain the 12 month calendar. Each row contains an employee and the columns are the days of the week. I have to keep track of 5 departments. I have 5 supervisors that are on different sheets that need to be included on the 6th sheet. I have set conditional formating to show that when I type "v" in a cell that is will go green. Is there a way to populate this information to another sheet without having to copy and paste?
View 2 Replies View RelatedI've got a field where I enter the current month, from this I derive a file path which is stored in A4.
ie: A1 = '01/11/2011' (UK Date format)
A4 = '\myserverdirectory20111111MyFile.xls'
What I'm trying to achieve is to then have a link in cell A6 that will link to the file in A4. I can specify my cell reference as this will never change, just the file path.
So, if I changed A1 to '01/12/2011' my A4 changes to '\myserverdirectory20111211MyFile.xls'
I'm trying to set A6 to something like ="&$A$A4&"!B1 to get the value of B1 on my referenced sheet.
I have an excel workbook that a colleague of mine is working with. each time she opens it, she gets a message telling her that a .xls file could not be found, and a web-address specifying the file path. We went through the workbook together and looked at all of the formulas and i cannot find anywhere that this address is referenced. I also looked for any code, but there was nothing showing on any of the worksheets in the entire workbook. I broke all the links to other workbooks, used find and replace to search for the path, and to search for all the formulas. I also copied the tabs to a new workbook, but to no avail.
View 2 Replies View RelatedI have a workbook that gets saved many times with many different filenames.
All I want to do is have cell A1 display as text the name of the file.
eg similar to the date function, there should be a = filename function .
If I have a two files called test1.xls and test2.xls for example. Is it possible to link test1.xls to test2.xls by putting the filename in a cell?
i.e. In test2 put the filename in say A1 and in the link put the path and instead of naming the file you want to link to put in A1 instead.
I know it doesn't work if you do it that way I was just wondering if there was a way to do it.
I currently have a file that is linked to an external workbook that is referencing a certain cell from a certain tab. The below is what Column A & B look like right now. What I'm wanting to know is is there a way to have the formula in Column B automatically reference the name in Column A (which is the tab name in the external workbook) instead of having to manually change the formula when the value in Column A changes?
In other words, I want everything in the external reference formula to remain the same except have the person's name change dynamically...
Mary
='X:Anderson CorporationEmployees[Work Hours]Mary'!$F$42
Joe
='X:Anderson CorporationEmployees[Work Hours]Joe'!$F$42
Frank
='X:Anderson CorporationEmployees[Work Hours]Frank'!$F$42
Michelle
='X:Anderson CorporationEmployees[Work Hours]Michelle'!$F$42
Sam
='X:Anderson CorporationEmployees[Work Hours]Sam'!$F$42
I'm trying to use some data from other workbooks in a formula. However, the path to the other workbooks we are using will often change.
I think that the user can enter the path to the other workbook in a cell in this workbook. However, I'm getting errors in my formula and am not sure how to make the formula use the cell to reference the location of the workbook that has the data that we want.
This is what I have, but it's giving me an error.
=Countif(' & 'Instructions'!B9 & Planning'!$I$3:$I$100, "C")
'Instructions'!B9 contains the location of the other workbook in the format \path[filename] Planning'!$I$3:$I$100 is the location of the range in the other workbook.
I am trying to write a code in VBA to collect data from several weekly workbooks, and list them up in a monthly workbook.
These weekly workbooks change names depending on the week, for example, we have:
Report week 1.xls
Report week 2.xls
Report week 3.xls
Report week 4.xls
Each report has the same layout, so the data I want will be in the same cells. However, as the report names change as the weeks in the year go on, I can't write the code for weeks 1-4 as they will not work with week 5 onwards!
I have found out how to chose a file of my choice using:
Sub GetImportFileName()
Dim FInfo As String
Dim FilterIndex As Integer
Dim Title As String
Dim FileName As Variant
' Set up list of file filters
FInfo = "All Files (*.*),*.*"
' Display *.* by default
FilterIndex = 5 ......................
I have the following formula in my workbook:
=COUNTIF('\caltulfs1DATAProjectsF2014FB-979916 - EQUATE7.0 DOCUMENTATION7.01 DOCUMENT REQUIREMENTS & RECORDS[FB-979916 DSI FORM.xlsm]Customer Tracking'!A12:A86,"past due")
When I open the master file I want my links to automatically update and open the referenced files without having to open the source document for each value. Can this be done
Currently I have a database in an Excel template. After a user creates a new workbook from the template, a macro button on sheet 1 brings up a window (a userform) to allow selection of items from the database sheet. The item selected is entered on sheet 1 by means of VLookup. That works fine, but to edit the database correctly it is necessary to open the template itself. This is not a user-friendly method.
I figure the best way to solve this dilemma is to separate the template from the database. That is, make the database sheet into a workbook. This I've done. Here's the problem: What code is there that links the list box in the userform (of the template) with the closed database workbook? Is it even possible? If a file path type code is required, it may work on my computer, but when I use my template and database on another computer, the code doesn't work.
Another problem, and similar is this: I'd like to be able to have a macro button that opens the database from the new workbook (which was created from the template) in order to edit the database. If having a template seems to be my problem, I must have it since each workbook created from it is a different project.