Changing part of a link for multiple cells in Excel 2

[curlycord]

Is there a way to update one cell and it updates/change part of the other cells at once?

I have a list of downloadable files on a special system and it does not have an html index file.
I myself can easily create one and upload it but clients that have the system cannot so I thought maybe Excel can help out.
I have them enter the IP address in one cell and it updates all the links.

This would be the one cell I would like to just enter the IP address:
192.168.1.200

This would be the list of links where I wwould like the IP address's to be updated by the above cell:

http://192.168.1.200/files/filename1.exe

http://192.168.1.200/files/filename2.exe

http://192.168.1.200/files/filename3.exe

http://192.168.1.200/files/filename4.exe

---
So I just change:
192.168.1.200 to 10.10.70.80

and end up with:

http://10.10.70.80/files/filename1.exe

http://10.10.70.80/files/filename2.exe

http://10.10.70.80/files/filename3.exe

http://10.10.70.80/files/filename4.exe

Thanks


________________________________________

=----(((((((((()----=

http://www.curlycord.com

Toronto, Canada

Add me to LinkedIN

 

[Andrzejek]

Assuming your IP address is always in cell A1, and your links start in cell A2 and go down without any empty cells, run this [tt]ChangeLinks[/tt] macro:

Option Explicit

Sub ChangeLinks()
Dim intR As Integer

intR = 2
Do While Range("A" & intR).Value <> ""
    With Cells(intR, 1).Hyperlinks(1)
        .Address = Replace(.Address, Split(.Address, "/")(2), Cells(1, 1).Value)
        .TextToDisplay = Replace(.TextToDisplay, Split(.TextToDisplay, "/")(2), Cells(1, 1).Value)
    End With
    intR = intR + 1
Loop

End Sub

I am sure somebody can come up with more elegant way to do it....


---- Andy

There is a great need for a sarcasm font.

 

[strongm]

Probably simpler just to use a worksheet function ..

So you have a cell with your IP address (and have named it IPAddress), then a table with all the file names and a column for the URLs. In the URL column the formula you want is (assuming filenames - as in /files/filename1.exe - are in column B)

=HYPERLINK("

https://"&IPAddress&B2)

Just copy that down the table, et voila.

 

[curlycord]

I have not dabbled in code or macros with excel yet but a star for both your efforts.

I have complied a sheet using strongm's method in this case.

I have a cell in column B with the name as IPAddress

I have all the file locations in column C
/files/filename1.exe
/files/filename2.exe
/files/filename3.exe
/files/filename4.exe

I have hidden column C

I have hidden the Row that contains "Column1" header (or table name?) with the arrow drop down.

All works great but I would like:
- To show just the name of the software (text that I choose, not the file name) instead of the actual full web link.

Doable?

Thanks!





________________________________________

=----(((((((((()----=

http://www.curlycord.com

Toronto, Canada

Add me to LinkedIN

 

[Andrzejek]

text that I choose, not the file name

And where on the sheet is this text?

You can add it to your Formula as a [blue][friendly_name][/blue] parameter:
[tt]=HYPERLINK("

https://"&IPAddress&C5,

[blue]"Click Here"[/blue])[/tt]

and if your chosen text is in column D:
[tt]=HYPERLINK("

https://"&IPAddress&C5,

[blue]D5[/blue])[/tt]

---- Andy

There is a great need for a sarcasm font.

 

[curlycord]

I just used your previous solution before the edit:
=HYPERLINK("

https://"&IPAddress&C5,

MID(C5, 14, 50)
Where in the brackets:
C5 = /files/filename1.exe (the column that I have hidden)
8 = the f which is the first letter of filename1.exe
50 = allows for any file name up to 50 characters.

As for the new edited post:
=HYPERLINK("

https://"&IPAddress&C5,

"Click Here")

I tried that too and works (and will stick with that)
The only thing with that is it renamed all cells (file links) to the same name but I was able to edit one cell after the other pressing "tab" to move to next cell after each edit.

"=HYPERLINK("

https://"&IPAddress&C5,

D5)"
I will try this later

Mission accomplished!
Thanks again



________________________________________

=----(((((((((()----=

http://www.curlycord.com

Toronto, Canada

Add me to LinkedIN

 

[Andrzejek]

I was playing around with the =HYPERLINK() function, but realized you don't want the file name as a [friendly_name] in your HYPERLINK, tat's why I edited my reply.
But, as you can see, all of these attempts work (I hope)


---- Andy

There is a great need for a sarcasm font.