I want to be able to have two separate hyperlinks in one cell. First, is it possible?
Code:
=IF(SETUP!$C$7="Lean",HYPERLINK("#'CORE SYNERGISTICS (LEAN)'!C6","Core Synergistics"),
AND(HYPERLINK("#'CHEST & BACK'!C6","Chest & Back"),HYPERLINK('AB RIPPER X'!B7,"Ab Ripper X")))
I'm trying to use a formula in conditional formatting to highlight a cell red if the cell contains a 0 but the date shown in another cell has passed. I want to copy the formatting throughout a column but I don't want the cell to highlight if there is no date in the other cell concerned.
View 11 Replies View RelatedI have a spreadsheet and within the first sheet there are lots of hyperlinks to other cells within sheet 1 (my template).
I have made a small macro which very basically makes a copy of sheet 1 (my template)
The hyperlinks in the template are explicit and as they are copied to sheet two, they still reference back to cells in sheet 1. I need the hyperlinks to be relative, so that when I make a copy of the template the hyperlinks are copied and make reference to cells in the new sheet. I cannot work out how this should be done
I have a macro (Photo_Hyperlinks2) that creates a hyperlink for every file found from a specific folder in Column A of Sheet 1.
The rest of Sheet 1 is my long version of doing what I want the macro to do... place the hyperlinks at the right spot. Sheet "Checksheet" makes a hyperlink of the already made hyperlinks (from Sheet 1) ONLY if the hyperlink in Sheet 1 is found. Same goes for the hyperlinks (1), (2), (3), and (4).
I really want to get rid of having to create a Sheet 1. I'm pretty sure there's a way to tell a macro to place the hyperlinks that contain "id" and/or "est" on the right row number in sheet Checksheet, and that if that row (in Checksheet Sheet) contains a (1) -(4), put it in the right cell, while also ommiting the first part of the name (ex: from 123445E(1) to (1)) ; I can live without that part if it can't be done.
Sheet "Checklist Using C-G Columns" shows the hidden columns not seen in Sheet "Checklist". Those manually inputed numbers are there mainly to tell that if more than 1 of the cells on the row are filled out, then to create a new row below it, and to continue the hyperlinking with (5), (6)... etc.
The final product is supposed to look like Sheet "Ideal Checksheet", except that ideally links (5)... etc. should work, and columns C-G are hidden again.
I need to separate row data from one workbook into separate workbooks based on cell data. Currently I filter the data, copy and paste it to a new workbook and save it. It's a tedious process and was hoping to find a way to automate it. I have attached a sample file. In this instance, I would like a separate workbook for the filtered data in Column 1 and then all the row data gets copied to new workbook. So all of Pennsylvania data would get copied to new workbook, then all of the Michigan data gets copied to a new workbook. I have also attached an example of the end result that I need.
View 9 Replies View RelatedI would like my hyperlink destination to move with the original cell (like links do) if I insert or delete a row above. The destination is on a separate sheet within the same work book.
View 3 Replies View RelatedI'm attaching an example.. Basically I am using Hlookup to bring some data over from another tab. Is it possible for the data coming over (in this case a brief description) to also bring the hyperlink that's associated with it. In other words I want someone to put in the code P5 which is Proof Load Test. When they put in P5 Hlookup will post the description of what P5 is in the cell below. Then from there I want the user to beable to hyperlink from the description to get to the summary located on a different tab..
View 5 Replies View RelatedAny way to have multiple hyperlinks to various files in one cel?
I want to the hyperlink to change based upon information entered in which i want it to copy an address from another sheet using the VLookup command.
I've set up a macro to create around 50 http links (sites which i need to monitor every week but whose addresses change slightly every month)
I've also got a macro to open up all these web pages at once so I can look at them in internet explorer.
When these links are nicely created into cells the http link isn't recognised straight away by excel (e.g. doesn't go blue & underlined) until i click in the cell & change or return the cell value - as part of the autocorrect function i think.
Is there a way of getting excel to automatically recognise the http links without having to go individually into every cell.
I'm putting together a userform and all the values and ending up in the correct place except for the two hyperlink cells.
I think it is because I've got the anchor wrong but Im not sure how to correct. I'll put the whole form in so if there is something fundametal going wrong in the early stages I can be put right, but the hyperlink issues are down the bottom at the Offset 16 and 17.
VB:
' Write data to worksheet
RowCount = Worksheets("Resources").Range("A2").CurrentRegion.Rows.Count
With Worksheets("Resources").Range("A2")
[Code]....
I have a spreadsheet that I have various formulas and sheets. To simplify:
Sheet 1: Columns A & B have Name and Code Number. I can add additional names and code numbers to these two columns whenever necessary.
Example:
Tom 874
John 385
David 712
Hidden in adjoining columns are formulas to automatically sort them via Code Number. Next to that are the columns showing the sorted information, sorted by number.
John 385
David 712
Tom 874
Additionally, I have a seperate sheet for Tom, John & David. Since I put Tom in first, he's Sheet 2. John is Sheet 3, and David is Sheet 4. I can use hyperlinks so when I click on John, it goes to Sheet 3; David Sheet 4; Tom Sheet 2.
Now, let's say I add Kevin 192 to the next row. The hidden columns are set up so that it'll automatically resort Kevin to the top, with John, David & Tom in the next rows down.
The issue I have with the hyperlink is that it's cell specific, not content specific, as far as I know. I would like the hyperlink to move with John's info so that it would still go to Sheet 3. Same with David and Tom. Unfortunately, after the resort, If I clicked on Kevin's name (which is now at the top of the list), it would go to John's sheet, since the hyperlink is attached to that cell.
How to allow a hyperlink to remain with the content, versus the cell? I would prefer it to be not a macro, but I'll take a macro over nothing!
A list of stock symbols runs down one column. In the next column runs a list of the corresponding companies.
The companies are linked in the following fashion: http://www.smallcapinvestor.com/quotes?symbol=ug
For the above example, "ug" is the stock symbol for the company United Guardian.
My question:
Is there a way to insert the text of a cell into a hyperlink? For example, how would I achieve this logic in Excel: http://www.smallcapinvestor.com/quotes?symbol=[CELL CONTENTS HERE]
I have a spreadsheet of various works-in-progress items, the first three columns of which refer to relevant documents, effectively the request, the estimate and the work order, i.e. RXXX-0001, EXXX-0001 & WXXX-0001.
So the text is entered as above (without file extension as it's also our reference number), and I'd like each to be linked to their respecive .doc files, which are held in different locations, i.e. \servernamefolderRequests or Estimates or Work Orders
e.g. \servernamefolderRequestsRXXX-0001.doc
There's a backlog of over 300 items (so 900 links) so I don't want to go through and manually create the links and as each day can see over 12 items being created it'd be much easier to have it done at the click of a button
Most of the solutions I've seen involve creating a new cell/button with the hyperlink in it - I just want to effectively "right click, create hyperlink" but with the cell contents inserted as the file name (adding ".doc") and with correct folder name too.
FYI the cells are adjacent to one another e.g. A1, A2 & A3 so the selection can be Offset (0,1) for the estimates and work orders.
I don't mind if it ends up being a case of running the macro on each line - some links have already been created already.
how can I change the following code so that the cell background colour remains the same. the below code unlinks the hyperlinks but deletes the backgroundcolour also of all the cells.
Code:
Private Sub Button3_Click()
ActiveSheet.Hyperlinks.Delete
Range("A7:W100").Select
[Code].....
Creating email drafts with the use of VBA in excel.
I've used some of his code to create an email draft to send a particular range within my excel spreadsheet. The trouble I'm having with it is keeping the hyperlinks within each cell in the range which will take the user to a particular website. How do I keep this formatting when the range is copied into the body of the email.
Example Cell A10 = HYPERLINK("URL","Google")
The hyperlinks are lost. How to keep these? Here is the code
Sub Mail_Selection_Range_Outlook_Body()
Dim rng As Range
Dim OutApp As Object
Dim OutMail As Object
Set rng = Nothing
On Error Resume Next
Set rng = Range("EmailRange")
[Code] ........
I receive an extraction from AutoCAD that lists the electrical devices in a drawing. I don't have any problems extracting the letters. I have a problem extracting the device number and the device number extension.
The device label extraction is similar to this:
DCM1005-1
DCM1005-10
DCM1005A
MTR1005-1
MTR1005-10
MTR1005A
I want to create 3 columns from the device label: (I separated the column with commas)
A1, B1, C1, D1
DCM1005-1, DCM, 1005, 1
DCM1005-10, DCM, 1005, 10
DCM1005A, DCM, 1005, A
MTR1005-1, MTR, 1005, 1
MTR1005-10, MTR, 1005, 10
MTR1005A, MTR, 1005, A
How can I separate the following numeric/text combination into two (2) separate columns in Excel?
302ALTO
406AMZN
451AMRC
404AMAD
605ANCC
405ADRC
The result would be:
302 ALTO
406 AMZN
451 AMRC
404 AMAD
605 ANCC
405 ADRC
I'm trying to separate text from numbers into two separate cells...
Essentially, I would like the users to copy and paste data into Column A, as seen below. Then, hopefully by formula separate the text characters into Column B and the numbers into Column C.
Input: Output 1: Output 2:
Col A Col B Col C
Wells 123 Wells 123
Wells 1234 Wells 1234
Wells Fargo 123 Wells Fargo 123
Wells Fargo 1234 Wells Fargo 1234
Wells Fargo Inc 123 Wells Fargo Inc 123
Wells Fargo Inc 1234 Wells Fargo Inc 1234
Ideally, I would like to do this with a formula...
I have 10 very large workbooks that are all setup in the same format. In column Z is a numerical value from 1 to 83. I have been trying to filter the sheet and then copy one at a time from 1 to 83 but that takes a LONG time especially when there is 10 workbooks to do.
Is there anyway I can run a function or macro or something that would just automatically look down the column Z and put each row into a it's own workbooks?
I have attached a sample of what the workbooks look like right now.
Sample123.xlsx
I have a giant data sheet. I have to cherry pick certain data out of the list and send it to certain associates, in a new spreadsheet.
I want to do this automatically.
I can create the macro to select the data I want, and copy it over (values only), but I cannot find out how to do it in a new sheet with a new name that automatically gets generated with a unique filename (probably using the date).
Essentially, I want to copy my data spreadsheet (post-filter) into a new spreadsheet titled X mm-dd (where X is whatever I pick). I then want to delete several rows from it.
Ideally, the macro would do this (I will put a * next to things I can handle the code for, to save work):Filter Data *
Copy Data *
Create a New Spreadsheet named (Person's Name) mm-dd.xls
Paste Data (values, formats, column widths) to the new spreadsheet *
Delete Several Columns from the new spreadsheet *
Rename the sheet it's been pasted to
Re-filter data (new filter) *
Copy Data *
Paste Data (values, formats, column widths) to the same spreadsheet, only Sheet 2 *
Set the header of the spreadsheet with (Person's Name) and the date
Save the new spreadsheet
There will be as few as 1 sheet (in which case I would only want one worksheet in the new excel file), or as many as 4 sheets. I don't need a fancy way to do this, as it will only be done once a week, and I can just make the various cases required to do it even if it isn't pretty, but I can't figure out how to manipulate other files that haven't been created yet...
I'm working on a spreadsheet that includes items I sell as well as the packaging it goes in.
The formula I'm looking for basically will tell a cell that if one cell (# of box) is equal a certain # it will add two separate cells by the weight of the box.
I have cells for each of the following: Weight in lb and weight in oz of the item (no box), a cell which holds the number of the box (ranging from 1-15) and two cells with weight in lb and weight in oz which would be the total weight of the item + box.
So, for example, if weight of item equals 1 and box cell equals 2 then it will put the weight of item + weight of box into a third cell.
Here's a picture of my current spreadsheet : [URL] .....
Basically, I would like to do something such as if P3 = 5, then Q3 will equal N3 + U6 and R3 will equal O3 + V6.
In my spreadsheet, certain transactions are labeled as "Fees" in Row G. I need any amounts in Row D to be negative if they are labeled as "Fees" in Row G.
I could simply find them manually and make them negative, but the logic needed in this question will help me with similar problems I am facing.
I am thinking it is probably some combination of the "IF" command.
I have a string that has an IP that doesn't follow a fixed length.
Eg:
10.248.242.65Microsoft SQL Server Compact 3.5 Service
10.248.242.5EOL/Obsolete Software: Oracle Java SE/JRE/JDK 6/1.6
10.248.251.144Microsoft Sync Framework Service Pack
seperate that IP and the String. I tried the below formula =REPLACE(A2,MIN((FIND({"M","E","S"},A2))),0,"?")
It works fine for the above examples, but i want a formula that can seperate the string after the IP ranging from A-Z or a-z
getting any formula so that i can determine atleast the position of the First Letter(A-Z,a-z) from left.
how do i separate information like this:-
01/08/2007,09:54:27,27.05,27.06,27.05,361300
let say its in cell A1 & i want first information before the First , to appear in B1 which is 01/08/2007
then the information before the second , to appear in C1 which is 09:54:27
and so on
I have a listing of numbers in different catagories such as the following:
Type A:
1
Type B:
2
Type C:
4
Total:
7
I want to be able to have a cell automatically calculate to say:
Type A:
"(1/7)"
Type B:
"(2/7)"
Type C:
"(4/7)"
with that exact formating.
I have a dataset with full addreses listed in one cell. I would like to use "text to columns" to separate the data but am unable to use the text separater in the wizard. When I open the text to columns wizard the data appears to be divided with a "square" but I cannot add it as "other" for the delimeter. see the attached.
View 2 Replies View RelatedI have a cell with several values; however, Excel seems to only "see" the first item. I have tried to use Text to Columns with no success. I have tried copying and pasting the cell in a variety of formats with still no luck. I think a macro may work, but I am not skilled enough to know what to do. Here is an example of the cell data:
"PRT-BNS-SVR-01/p;2
PRT-BNS-EOC-01-5.5.04/-;1
PRT-BNS-SRD-01/t;4
PRT-BNS-SDD-03/f;1
PRT-BNS-SRD-03/g;1
PRT-BNS-SCI-01/bn;1
PRT-BNS-SAS-01/t;1
PRT-BNS-SDD-01/f;1
PRT-BNS-CIA-01/p;2
PRT-BNS-SVCP-01/r;2"
In this case, the first item is PRT-BNS-SVR-01/p;2. I need separations between the / the ; and between each item. I am not sure where the quotes come from, as this only happens when I copy and paste to text. I can not see them in Excel. When I use Text to Columns, the first material will separate, but it's like the others so not exist.
I need a VBA code to take everything after a certain word and put it in a new cell below it. The cell would have View More in it most of the time, and sometimes spaces will be messed up, so it will have a name after it but it should actually be in the cell under it. So if the name is A Maple, it will say View MoreA Maple. So the A Maple need to go in a cell below it.
View 6 Replies View RelatedC5=ACDefg-0087HzY
In D5, I want 1st 4 letters (EXACT)
In E5, last 10 letters (EXACT)
How to accomplish?
Ans
D5=ACDe
E5=gf-0087Hzy
I am try to separate my data from A1 to B1,C1, D1....... Here is what it look like in A1
C102, C110, C114, C116, C118, C120, C125, C128, C130, C131, C132, C134, C135, C139, C140, C143, C144, C19, C21, C22, C27, C30, C38, C40, C50, C56, C57, C59, C6, C60, C61, C69, C85, C88, C90, C94, C98
I want to separate them into several cells. Each cell can only have 30 characters or less. You can not cut it in the middle of the data.
After your separate B1 should be "C102, C110, C114, C116, C118," (30 characters)
C1 should be "C120, C125, C128, C130, C131," (30 Characters)
D1 should be "C132, C134, C135, C139, C140,"(30 characters)
E1 should be.......... till the end.
I try to several functions too. But it does not works.