Solved

excel hyperlink

Posted on 2014-10-26
7
174 Views
Last Modified: 2014-10-26
Hi guys
=HYPERLINK("http://www.website.com/ordered_item.asp?item=" & B2 & ,E2 )
I have an XLS spreadsheet that contain a column with the above formula
Now
I need to delete columns B and E and keep the hyperlink functioning
Is there a way to do that using copy & paste Special?
thanks
dory
0
Comment
Question by:dory550
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 15

Expert Comment

by:Haris Djulic
ID: 40405024
Not clear.. You want to remove the columns B i E from formula just do this : =+HYPERLINK("http://www.website.com/ordered_item.asp?item=")

or something else?

Please provide sample output to be more clear?
0
 
LVL 9

Expert Comment

by:Christopher Jay Wolff
ID: 40405118
Hi.

What comes after your link address is the friendly name of your clickable link.  So if you want someone to see "&B2, E2" as the text in your hyperlink cell on which to click, then "&B2, E2" has to be in quotes.

If the text value of Cell B2 is "Click here for items" then then you should delete the & and use...
=HYPERLINK("http://www.website.com/ordered_item.asp?item=", B2) which will allow your hyperlink cell to display "Click here for items" rather than the entire URL as a clickable link.

And if you delete column B, then your hyperlink should still work but the link to click on will simply be a "0" since column B no longer has data in it.

Is this what you need?
0
 
LVL 5

Accepted Solution

by:
ReneD100 earned 500 total points
ID: 40405261
I think you currently have it set up the following way:
Column B contains the item link that needs to go into the URL, Column E is a 'friendly' description of the item.

I don't think there is an easy way to do this with copy/paste, however you could use some VBA coding to replace the original Hyperlink:
Public Sub UpdateHyperLinks()
    'Assuming the Hyperlinks are in column A, from A2 to A20
    Const colHyperlink As Integer = 1 'A=1, B=2, etc
    Const colItem As Integer = 2 'linkitem in column B
    Const colFriendly As Integer = 5 'friendly name in column E
    Const rowStart As Integer = 2
    Const rowEnd As Integer = 20
    Const URL As String = "http://www.website.com/ordered_item.asp?item="
    
        Dim iRow As Integer
        Dim strLinkItem As String
        Dim strFriendlyName As String
        Dim strHyperLink As String
        Dim r As Excel.Range
        Dim strFormula As String
        For iRow = rowStart To rowEnd
            strLinkItem = ActiveSheet.Cells(iRow, colItem)
            strFriendlyName = ActiveSheet.Cells(iRow, colFriendly)
            strHyperLink = URL & strLinkItem
            Set r = ActiveSheet.Cells(iRow, colHyperlink)
            strFormula = "=Hyperlink(""" & strHyperLink & """ , """ & strFriendlyName & """)"
            r.Formula = strFormula
        Next
   
End Sub

Open in new window


This code could be a lot shorter, but I have included some extra constants and variables to illustrate the usage.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:dory550
ID: 40405271
Thank you guys
Sorry
I had a small error in my original question below is the corrected hyperlink function
=HYPERLINK("http://www.website.com/ordered_item.asp?item=" & B2w  ,E2 )
in short
E2=product_Number  B2= product_description
Below is the same hyperlink showing sample contents of  E2 and B2
=HYPERLINK("http://www.website.com/ordered_item.asp?item=" & "8881234"  ,"Duracell AA -4pk  batteries"  )

The description is a hyperlink's friendly text
When clicked the user gets a webpage with price and expanded details and an image
Basically I am trying to create a hyperlink column that will show the contents of E2 and B2
Instead of the variables E2 and B2
Hope this is a little clearer
Thanks
Dory
0
 

Author Comment

by:dory550
ID: 40405279
oops
the fourth line in my last post should read
=HYPERLINK("http://www.website.com/ordered_item.asp?item=" & B2  ,E2 )
Dory
0
 
LVL 5

Expert Comment

by:ReneD100
ID: 40405295
>> I had a small error in my original question below is the corrected hyperlink function
Yeah, I figured that was the case ;) but my solution will still get you what you need.
0
 

Author Closing Comment

by:dory550
ID: 40405532
ReneD100
Thanks for your help
Your solution is fine
dory
0

Featured Post

Office 365 Training for Admins

Learn how to provision tenants, synchronize on-premise Active Directory, and implement Single Sign-On with these master level course.  Only from Platform Scholar

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Certain Excel spreadsheets will randomly drop lines 3 46
Windows ICD FFU Issue 4 105
Why use this lambda? 12 94
Need help with pivot summary or report 17 46
As freelancing is becoming more and more common in the tech industry, certain obstacles are proving to be a challenge to those who are used to more traditional, structured employment. This article is meant to help identify such obstacles and offer a…
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This video teaches the viewer how to align pictures around text while keeping the text properly aligned in the document.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

737 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question