hyperlink to website and then to information in a certain cell

[Ernie]

I have a spread sheet set up with a hyper link to a web
page:http://fs.xxx-xxxx.com/cgi-bin/fieldsup/getRemedy_isd.cgi?ticknum=0000000XXXXXXXX,XXXXXXXX

the 00000XXXX represents a ticket number that I have to manually enter for
every cell in order for it to pull that ticket number up in my website.
Does anyone know what formula I can enter here that will automatically pull
the number that I have already input in the cell on my excel sheet, so that
the number will not have to be manually entered into the hyperlink for each
and every cell?

 

[Ron de Bruin]

Use the Hyperlink function

With the number in A1 you can use

=HYPERLINK("http://fs.xxx-xxxx.com/cgi-bin/fieldsup/getRemedy_isd.cgi?ticknum=" & A1)

 

[ryguy7272]

No, no, no... Look at this sample code:
Sub HistData()

Application.ScreenUpdating = False

Dim str1 As String
Dim str2 As String
Dim c As Range

Dim Stocks As Range


Dim bFound As Boolean
Dim ws As Worksheet


Set Stocks = Application.InputBox( _
"Type 'Symbols' in the input box below", Type:=8)

For Each c In Sheets("1 - USA Firms, Import").Range("Symbols")

bFound = False
For Each ws In Worksheets
If ws.Name = c.Value Then
bFound = True
Exit For
End If
Next ws

If bFound = False Then
Worksheets.Add.Name = c.Value
End If

'----------------------------------------------------------
Sheets(c.Value).Select
Cells.Select
Range("A1:IV65536").ClearContents

str1 = "URL;http://finance.yahoo.com/q/ks?s=" & _
c.Value

With ActiveSheet.QueryTables.Add(Connection:=str1 _
, Destination:=Range("A1"))

.Name = str1

.Name = "ks?s=c.Value"

.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = True
.Refresh BackgroundQuery:=False
End With

End Sub

There's a little more to it, but that's what you need. Put the numbers you
want to use in a your sheet (mine is named '1 - USA Firms, Import'; change
the code to match your sheet name). The code starts looking at the code in
Cell A3 and goes to the end of the used range - Range is named 'c.Value'. Of
course, you'll have to record a macro, and get the actual URL and the
references; all comes from the recorded macro. Just try that and see how
far you get. Post back with specific questions.

HTH,
Ryan---

 

[Ernie]

This is a little above my head considering I am new to this.
My info is located on my computer at:
C:\My Documents\PM Template and the worksheet isistrict and the cell
is:I4

The ticket numbers are in the I range of cells and these are the numbers I
need for it to recognize and pull into the hyperlink where the 0000000XXXXXXX
is on the hyperlink so I do not have to manually do it.
How should this be written?

 

[Jacob Skaria]

Refer help on HYPERLINK() and in case you need more help get back with
examples..

If this post helps click Yes

 

[Ernie]

The example is in the 1st post.
I already have the hyperlink set up in my excel sheet which is
http://fs.xxx-xxxx.com/cgi-bin/fieldsup/getRemedy_isd.cgi?ticknum=0000000XXXXXXXX,XXXXXXXX)
This link pulls up my web site.

The 0000000XXXXXXXX represents a certain ticket number and currently I have
to enter this number manually in the link above on each cell for it to work
(I have to hyperlink with the actual number in every cell manually).
I want to be able to adust/add/modify the link to where it will
automatically identify and put the number I have already in a cell on my
worksheet:
Example: My Ticket numbers are located on:
C:\My Documents\PM Template and the worksheet is namedistrict
and the cell is I4
All of my ticket numbers are already posted in cell I4 on this spread sheet
and I would like to be able to just click it and it open the hyperlink with
that number so I do not have to manually input the number in the hyperlink
itself. I have thousands of numbers that I have to reference daily to that
website.
If it makes no sense I can share my personal email-If you can help with it.

 

[ryguy7272]

That link gives me nothing but an error. If you send me what you have now,
and specific instructions about what you want, I'll do this for you.

Thanks,
Ryan--
(e-mail address removed)

 

[Jacob Skaria]

I saw the link in the original post..I meant to ask what the relevance of xxx
before comma and after.

With number in A
=HYPERLINK("http://fs.xxx-xxxx.com/cgi-bin/fieldsup/getRemedy_isd.cgi?ticknum=" & A1,"Click me")

You can prefix 0's using the function ....
=TEXT(A1,"00000000000")

=HYPERLINK("http://fs.xxx-xxxx.com/cgi-bin/fieldsup/getRemedy_isd.cgi?ticknum=" & TEXT(A1,"00000000000") & ,"Click me")


If this post helps click Yes

 

[Ernie]

Due to security, I can not release the actual web site. xxx-xxxxx reprents a
an internal web link.

 

[Ernie]

That set of xxxx before and after comma-represents the actual number I have
in the excel spread sheet. If i put the number in there manually it will
pull up my link, but I do not want to have to manually put the numbers in
every single hyperlink in every cell. That is where i want to be able to
insert a formula that will identify the number in the spreadsheet and take me
to the pages.

I can email you the spread sheet so you can see what I am trying to
accomplish here.
(e-mail address removed)

 

[Jacob Skaria]

OK> Did you tried the suggestions

If this post helps click Yes

 

[Ernie]

The link has always worked when I put the number in after ticknum=.
But, I have thousands of ticket numbers daily on a spread sheet that I look
at and I do not want to manually enter this for every one of them.

("http://fs.xxx-xxxx.com/cgi-bin/fieldsup/getRemedy_isd.cgi?ticknum=I NEED A
FORMULA HERE THAT WILL IDENTIFY THE CONTENTS OF THE CELL, WHICH THE CONTENTS
ARE EACH INDIVIDUAL TICKET NUMBER. I would like to be able to just click
this cell (which has the ticket number in it) and it take me to the web page.
Otherwise I have to put the number in the hyperlink for every cell.

 

[Ernie]

Yes, put it still will not recognize the contents of the cell (Which the
contents is the acutal ticket number) and it pulls up a blank page on the web
site.
("http://fs.xxx-xxxx.com/cgi-bin/fieldsup/getRemedy_isd.cgi?ticknum=IF I CAN
GET A FORMUALA HERE THAT WILL ACTUALLY RECOGNIZE THE CONTENTS OF MY CELL, I
WILL NOT HAVE TO MANUALLY ENTER THE NUMBER ON EACH CELLS HYPERLINK ONE AT A
TIME, AND I HAVE THOUSANDS ON EACH EXCEL SHEET DAILY.

 

[Peo Sjoblom]

Why are you shouting (Caps)? People here are not employees of MS that get
paid answering questions and to do other people's work. People answering
questions do it on voluntary basis. Bear that in mind!


--


Regards,


Peo Sjoblom

 

[Ron de Bruin]

Have you test the formula with the number in A1 ?

 

[Ernie]

Yes, the origianl formula has a number in it and it will and has always
worked. But with that, I have to change the hyperlink in each cell with the
ticket number that is in the cell on my excel sheet.
If you email me at (e-mail address removed) I can send you the sheet so you
can see what I am trying to accomplish.

 

[Ernie]

Not shouting in no means Sir, was just making sure they could recognize
where I need a formula.
And caps does not always mean you are shouting, can be used to make sure
something is not over looked.
Bear that in mind!