Hyperlink with variable filename in address

[Fudog]

I need to set every cell in a column to hyperlink to the same web address:


http://www.dbinfinity.cargotel.com/backoffice/loadmaint.mcgi?load_id=


with the document number that is entered in the cell to be added to the end of the web address after the equals sign so that I can just enter it in and not have to set up a hyperlink every time I create a new row, therefore being able to enter the number in the cell, and link to the document.

 

[Luke M]

Welcome!

You should be able to use the hyperlink formula, e.g.

=HYPERLINK("http://www.dbinfinity.cargotel.com/backoffice/loadmaint.mcgi?load_id="&A2,A2)


Then just copy formula down as needed.

 

[Fudog]

It's seeing it as a hyperlink, but it is not adding the document number at the end thus giving me "cannot open the specified file" error.

 

[Luke M]

The document number is in cell A2, correct?

The formula is in another cell?


When I type 12345 into A2 on my machine, and I hover over the cell w/ hyperlink formula, the link is displayed as being:

"http://www.dbinfinity.cargotel.com/backoffice/loadmaint.mcgi?load_id=12345"

 

[prasaddn]

Hi Fudog,


Just a wild guess, have you copied Luke's formula correctly? The partition "&A2,A2" ? One (A2) reference to add value to string and other (A2) reference to display name?


Regards,

Prasad DN

 

[Fudog]

I just highlighted the column and under the insert tab selected hyperlink and pasted it there.

 

[Fudog]

How do you hover with hyperlink formula?

 

[Fudog]

Where's A2?

 

[Fudog]

Let me start over and explain in more detail; I use a spreadsheet to track loads of cars that I broker and ship, the actual work is done with a web based program (Cargotel)which designates the load numbers which I put on the spreadsheet. I want to be able to key the load number into that cell and have it automatically link to that load number in the web based program without having to do nothing more than enter the number and tab or click out of that cell. Explain it to me like a child and I can probably understand (not that small of a child)

 

[mr_hiboy]

Hi Fudog,


Type your URL in cell A1 and ID is cell B1, then in C1 type =HYPERLINK(A1&B1).


Should work a treat.


Paul

 

[mr_hiboy]

for clarity, without the dot


=HYPERLINK(A1&B1)

 

[Fudog]

I left this out, As I get new loads I insert the new lines from the top (insert) so everything shifts down as I enter the new loads.

 

[Luke M]

As the formula is referring to cells in the same row as itself, inserting new rows is not an issue.

 

[Fudog]

I enter this in: =HYPERLINK(A1&B1) and get this error: #NAME?

What does that mean?

 

[Luke M]

That's usually the error that appears when you've entered an invalid function name. Are you using a non-English version by chance?

Link to Microsoft's page:

http://office.microsoft.com/en-us/excel-help/hyperlink-function-HP010062412.aspx

 

[Fudog]

No, it's English, this should be simple, I don't get it. I enter a number in the top (below the label) cell in column 3, i need that number to become the filename in a weblink, i add lines from the top. (insert)

 

[Luke M]

So, there are 3 cells of interest. One cell has this portion of the URL, say cell A1:

http://www.dbinfinity.cargotel.com/backoffice/loadmaint.mcgi?load_id=


The 2nd cell is where you enter the number, say cell A2.


The 3rd cell contains this formula:

=HYPERLINK(A1&A2)


If you want the cell w/ formula to only display the number given in A2, you can modify formula to:

=HYPERLINK(A1&A2,A2)

 

[Fudog]

Still getting that stupid #NAME? error in cell 3 ?????

 

[Luke M]

Hmm. I'm afraid I can't duplicate your error...could you try uploading the file (refer to 3rd sticky in forum)?