I have a list of job names in a column. All names contain hyperlinks to a corresponding file. I need to retrieve the value of "C2" in each hyperlinked file and place it next to the original job name.
View 6 RepliesColumn C (starting at row 5) contains hyperlinks. I want to print the linked file, move down to the next cell, print that file, move on to the next, etc. FYI - The worksheets are from a seperate file.
View 2 Replies View RelatedIf, say cell A1, contains text such as:
"This Forum is wonderful, to see a great example for a post click here." <This is not an actual link here!>
Is it possible to have the "click here" part as a hyperlink, and not the entire cell?
Or- do i have to add an "image" or something similar to which i can add the hyperlink and then anchor the "image" to that cell?
Is there any way I can make it so an image is hyperlinked to a cell with specific text entered. After clicking on the image, the cell where the image is hyperlinked to changes text.
For example:
An image of a strawberry is hyperlinked to a cell which contains the word "strawberry".
After clicking on the strawberry image, the cell which contained the word "strawberry" now contains the word "strawberry1".
If you understand that, I would so love the help.
I also posted this is question here
http://www.excelforum.com/showthread.php?t=637811
Explanation of problem:
I have 2 files: data.xls and calculation.xls
In data.xls, I enter a value in cell A1
In calculation.xls, I go to cell B1 and enter a reference to cell A1 on file data.xls. The following formula is entered in cell B1 of calculation.xls: =[data.xls]Sheet1!$A$1
Ok so far so good. Now when I change the value in data.xls, the value is updated automatically in the calculation.xls cell. And this is done even if I first update the value in data.xls and then later open the calculation.xls file (it will ask me to update).
Now let's say I have calculation.xls closed and I am updating the data.xls file. I enter a new entry above cell A1 in data.xls. So now the contents of cell A1 are shifed down to cell A2.
When I re-open my calculation.xls file, it is still referencing cell A1 in data.xls. Is there any way for me to link it in a way so that calculation.xls would know that it now needs to reference cell A2 since that cell got shifted down?
If both files are open then it works fine. But the way this setup will work, I will not always have them both open.
Also, if I copy the contents of data.xls onto another sheet on calculation.xls and reference the cell in the same file, then of course it works. But I would like it to work the other way with the files separate.
I have multiple files in one folder: first.xlsx, second.xlsx, third.xlsx etc
In each of them the first cell is calle fist_indicator, second_indicator etc
In a new document i have a column A with all the names: fist, second, third
In a hidden second column i have ="="&"["&ZS(-1)&".xlsx]Tabelle1!"&"Z1S1" which gives the value of =[first.xlsx]Tabelle1!Z1S1 , =[second.xlsx]Tabelle1!Z1S1
So now when i try in column c to just say = R1C2 it gives a #NAME? error
Summarized, i have a manually filled in column A and with it i want to make another column that shows the first cell of each document (so it uses the fist colum + the other directions to show the first cell)
if i just add =[first.xlsx]Tabelle1!Z1S1 it works, but i want to be able to make this first.xlsx be changed according to the entry in column A.
I want to use a macro to open an existing file on my c drive where the reference to that file is contained in a cell.
E.g. In my sheet, the cell A1 contains the following text "C:Documents and SettingsTest". Therefore the file I want to open using a macro will be Test.xls.
However the file I wish to open (and therefore the reference text in cell A1) could change (and it could also change to be a word document) so it needs to be able to adapt when a new valid file (and document type) reference is entered into the cell.
Also, as an aside, when I want to change the file referred to in A1, the easiest way I've found is to find the file I want in windows explorer, go to properties and copy the file reference and then paste it into A1. However the reference (from the properties tab) seems to miss the ".xls" or ".doc" at the end (see above). Is this an issue? If yes, then I'm happy to manually type the .xls or .doc extension at the end.
Is there an easier way to do this?
I have the following (working) formula that pulls the largest value from an array in a closed workbook on a network share:
=LARGE('\pdfpcoclu2k302pd10fap2k01dist10AdminFISCAL SHAREDM806 ReportsFY 2008-2009Clarion countyJanuary 09[385-4009.xls]M-805'!$F$11:$G$41,1)
I want to be able to use a cell value within the open workbook to change the name of the referenced workbook (i.e. the 385-4009 portion of the formula) in the fromula
I have atempted to use the following:
=LARGE('\pdfpcoclu2k302pd10fap2k01dist10AdminFISCAL SHAREDM806 ReportsFY 2008-2009Clarion countyJanuary 09["'"p8"'.xls]M-805'!$F$11:$G$41,1)
but excel returns an error and highlights the following portion of the formula:
'\pdfpcoclu2k302pd10fap2k01dist10AdminFISCAL
I'm using a spreadsheet to assimilate data from a number of different files. Now I need to vlookup on this compiled data, but the vlookup cannot find the value because it is not looking at the value of the data in the cell, but rather the formula. How do I get vlookup to search a column by the value displayed in that cell, rather than the reference to some other file?
View 9 Replies View RelatedI'm trying to open a workbook using a macro, then saving the workbook that's just been opened using a cell value as the title.
This is what I have so far ... to open up the workbook and then run a macro from there .. this works fine
Sub Test1()
'
' Test1 Macro
' Macro recorded 08/11/2009 by Tracy
'
Workbooks.Open ThisWorkbook.Path & "/MultiFuel Expense.xls"
Application.Run "'MultiFuel Expense.xls'!TestSave"
End Sub
This macro is the one I'm struggling with. I need to save a copy of the workbook I just opened (currently named MultiFuel Expense) in the same directory using the cell contents of G2 as it's name with _expense on the end.
Sub TestSave()
'
' TestSave Macro
' Macro recorded 08/11/2009 by Tracy
'
ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "" & Range("G2") & "_expense"
End Sub
Trying to reference cells in a closed workbook for the first time, and am having problems. The formula in my cell is: ='H:Trial BalanceTESTING[12-07-2007.csv]12-07-2007'!$P2
At first it wouldn't work, and I always had a #REF! in that cell, but when I opened the csv file the correct value showed in the cell that has the formula. Is it not possible to have it work without me having to open the csv file as well?
I want to save my file as a pdf based on cell reference A4.
This is what i have
[Code] ....
i have a order form that is customer dependant. the cust name on the form is manually entered by the user within a msg box. i have specific cusotmer folders on a shared machines C drive where i would like to save these excel docs to.
i understand that the cust name must match the name of the file and there needs to be a way to save those that do not have specific folder matching what was entered as the customer name.
the overall goal is to have the file name saved as CustomerNameDeliveryDateInvoiceNumber.xls where CustName is from cell "M3", delivery date is "Q7" and InvoiceNumber is "Q1".
Sub OrderFormSave()
Dim strCustFileName As String
savefile = "\SrSharedDocsCSPSharedFILESCustomerOrderForms & strCustFileName & "
ActiveWorkbook.SaveCopyAs savefile
End Sub
I need to reference a cell to another cell in a different workbook (not opened), however the name of this workbook contains a variable in its name. Something like:
='C:[Week" & variable &".xls]R23'!$D$3
where variable is the number of the week. I have tried using a cell name instead of a variable: ='C:[Week=($B$3).xls]R23'!$D$3
and I get a dialog box to update values by selecting a file.
I'm trying to make a vba that would save the file with the end date that will reference to a certain cell that user selects it. Working Sheet is a name of a File, and Current Sheet is a name of the panel where user will select the date.
ActiveWorkbook.SaveAs Filename:= WorkingSheet("Control Sheet").Range("D3").Value & Format(Date, "yyyymm") & ".xlsm", FileFormat:=51
But here i seem to missing the location
And I tried this one:
ActiveWorkbook.SaveAs Filename:="X:Work2014WorkingSheet& Format(Date,"yyyymm"). & ".xlsm" _, FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
but it keeps popping up as 'expected end of statement' for yyymm?
I'm trying to get Mac OS X Excel VBA to understand this code however the ChDir reference and the filepath seem to be causing problems. I would like to specify in the code where the file should be stored but I did try removing the filepath section altogether but to no avail.
Option Explicit
Sub SvMe()
'Saves filename as value of A1 plus the current date
Dim newFile As String, fName As String
' Don't use "/" in date, invalid syntax
fName = Range("A1").Value
'Change the date format to whatever you'd like, but make sure it's in quotes
newFile = fName & " " & Format$(Date, "mm-dd-yyyy")
' Change directory to suit your PC, including USER NAME
ChDir _
"C:Documents and Settings USER NAME Desktop"
ActiveWorkbook. SaveAs Filename:=newFile
End Sub
i have an excel file that has several worksheet tabs most of which i have hyperlinks running on them, what i need to do is colate a couple of the sheets to a seperate excel file but when i try to copy them from the original file to the new file they will not copy because the info is hyperlinked!
View 9 Replies View RelatedI am trying to send email using and excel macro.
I would like to send an image that has a hyperlink in an email, so when the user receives the email they can click the image and it will take them to a webpage.
The code I have so far is this
With OutMail
.Attachments.Add "C:PanelImagesEmailer_EN.jpg"
.Save
.SentOnBehalfOfName = "Email@Address.com"
.To = cell.Value
.Subject = cell.Offset(0, 2).Value
.HTMLBody = "" & _
"IMG src=cid: Emailer_EN.jpg"
.Display
.Send 'uncomment to send
If I put text in the area after the link then the text appears as a hyper link.....
I am trying to automatically reference the contents of another file into my main file. Basically I have one schedule that I've automatically generated in AutoCAD, and another formatted template. The AutoCAD schedule is completely unformated.
I want to include a link to the AutoCAD scheule (a *.CSV file) on my main spreadsheet and have the autoCAD schedule populate my template. I've done some reading, primarily on here, and I think I need to use either the INDIRECT() or the INDEX() function.
I can get INDIRECT() to work, but it's fairly repetitive (not overly important, but still.....). More importantly, though, the schedule file has to be open, which I would like to avoid if possible. INDEX(), I just can't figure out how to work!
I currently have one spread sheet. At the top of that spread sheet there is an object that hyperlinks to cell A180. When i click that hyperlink, it take me to cell 180, but it appears at the bottom of the spreadsheet, rather than bringing it all the way up to the top of my current viewable screen.
View 3 Replies View RelatedI have a workbook full of several tabs. The main tab is the 'Report' tab, all other tabs are hidden. The information on the hidden tabs populate onto the 'Report' tab.
My question is, I have created a shape on the 'Report' tab to act as a button. This "buttton" is which I have hyper-linked with one of the hidden tabs. When the hyper-linked tab is showing, the button works fine. However, when I hide the hyper-linked tab, the button does not work. I would prefer to have just the 'Report' tab showing.
I have a table of many rows and columns of information, some of which is hyperlinked to seperate sheets in the same workbook, some are not hyperlinked at all. What I want to do is select a column and when a hyperlink is found going down the column, copy the sheet that it is linked to into a new book, then find the next hyperlink and copy that sheet into the same book as the first one. So if I have 25 hyperlinks in the column, I finish up with a new book containing 25 sheets in the order they were found. Hope someone can help me with this, it will save me hours of work.
View 9 Replies View RelatedIve spent a few hours adding email hyperlinks to data in column B.
I now want to sort the data in the worksheet into ascending order using the data in column A.
The problem is, none of the hyperlinks from column B sort with the data in column A, they are still in the cell locations I had originally entered them.
I have created a macro in excel 2010 which enable the file to save (extract) data into separate location and name. The vba code for macro is as follows: Question: How can I save this workbook with reference to the value containing in cell B2? (it is named temporary now - as defined in the code)
Sub aaa()
'
' aaa Macro
'
[Code].....
I have a blank file and a data file for current year with formulas.
So I am trying to take a couple of tabs from current year data file and pasting them into the blank file.
My question when is do that all the formulas get referenced to the current years file. i want to keep the same formulas but reference the tabs on blank file.
Is there a quicker way to do this rather and going into all the cells and changing reference from current years files data to blank files data.
How do I change a formula cell reference based on another cell's reference? I'm building a schedule that looks to a task's trigger and adds days based on that relationship. All entries in column "A" will be text and all cells in "B" will be the simple formula "=A2" or "=A3". Due date is calculated by adding the value in "C" to the preceding date in column "D". In the spreadsheet below, the trigger for "Budget set" is "Specs written" with 3 days added to the previous due date.
________A________________B_____________C_________D
1 Task___________Trigger_____________Days_____Due Date
2 Design begins__Proj OK______________10____10-Jan
3 Specs written__Design begins (A2)____5____15-Jan (D2+C3)
4 Budget set_____Specs written (A3)____3____18-Jan (D3+C4)
If the trigger for A4, "Budget set", changed from A3 to A2, is there a way that the formula that determines the due date in D4 could read the trigger cell reference in B4 so that the value in the corresponding row in column "C" is added in the date column?
I have a macro named "Attenuator10()"
I created a user from with a button for the use of that macro, however for use of the userform it wants a _Click macro.
What would i need to put into the _Click macro to refernce and perfrom "Attenuator10()"?
I am not very experienced with excel VBA and I am trying to populate a blank sheet in a file called Agent.xls by referencing a file called Agent_Export_03.xls I tried playing around with a few things and I don't know why it isn't working, I guess I don't know how to reference to a separate file. I need the macro to look at cells in C1 an depending on if they are different or not, populate my new excel file accordingly, but my reference doesn't work.
Sub Agent()
x = 2
counting = 2
countitem = 2
Do While [Agent_Export_03.xls]Sheet1!RxC1.Value <> ""
b = x - 1
If [Agent_Export_03.xls]Sheet1!RxC1.Value <> [Agent_Export_03.xls]Sheet1!RbC1.Value Then..............
I have a table that displays data from another worksheet. This is what the cell reference behind the table look like:
View 2 Replies View RelatedI have lets say 12 months of data. I have formulas that reference the latest 6 months. When I insert a new column to input a new month, how can I make the formulas include the new months without manually updating them.
EXAMPLE:
12 months of data exist in cells B3:M3 going from B3(oldest) to M3(newest). Formulas reference latest 6 months of data in cells H3:M3. When a new month hits, I insert a column after column M.I would like the formulas to now reference cells I3:N3 which is now the newest 6 months.