• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Addressing cells in the Hyperlink formula

B

Bernard46

New Member
I have the following formula in my spread sheet:

=HYPERLINK(env("CC")&"\photos\"&A1480&".pdf","photos\"&A1480&".pdf")

I would like to eliminate the use of the Environment variable CC in this formula because it makes portability of the spread sheet somewhat difficult - not everyone is aware of Environment variables or wants to insert them and reboot their system.

The root directory for my spread sheet is the directory above \photos so I ought to be able to use the Related Documents default location - this makes life very easy. However I want to be able to keep the reference to the value contained in (in this example) cell A1480 since it contains the first part of the name of the file I wish to link to (I don't think it is relevant but I'll mention it just in case - the Hyperlink formula resides in another cell in row 1480). I've tried all sorts of combinations of ", !, $ and & but I just don't seem to be able to generate a valid Hyperlink formula that eliminates the need for environment variables yet retains the reference to the cell. If I can achieve this it makes my spread sheet very portable and it also allows me to copy and paste this formula across many rows, thus saving a lot of work. Can anyone help?
 
To get current workbooks path in a cell using built-in functions:
in a cell, say A1, put:
=TRIM(LEFT(SUBSTITUTE(CELL("filename",A1),"[",REPT(" ",999)),999))

So, for your hyperlink function:
=HYPERLINK(TRIM(LEFT(SUBSTITUTE(CELL("filename",A1),"[",REPT(" ",999)),999))&"photos\" & A1480 & ".pdf")
 
Luke M said:
To get current workbooks path in a cell using built-in functions:
in a cell, say A1, put:
=TRIM(LEFT(SUBSTITUTE(CELL("filename",A1),"[",REPT(" ",999)),999))

So, for your hyperlink function:
=HYPERLINK(TRIM(LEFT(SUBSTITUTE(CELL("filename",A1),"[",REPT(" ",999)),999))&"photos\" & A1480 & ".pdf")
Click to expand...

Wow! That's brilliant. I changed your suggestion slightly to
=HYPERLINK(TRIM(LEFT(SUBSTITUTE(CELL("filename",A1),"[",REPT(" ",999)),999))&"photos\"&A1480&".pdf","photos\"&$A1480&".pdf")
to include the hyperlink display text and it works fine for the first place I inserted it.

There is just remaining one problem - I would now like to copy and paste this into other rows in the spread sheet and have it update the link to the column A cell - this does happen, however I also get an error because it also updates the reference to A1 after "filename" as below - any idea how this can be avoided please?
=HYPERLINK(TRIM(LEFT(SUBSTITUTE(CELL("filename",#REF!),"[",REPT(" ",999)),999))&"photos\"&A1418&".pdf","photos\"&$A1418&".pdf")
 
Hi Bernard ,

The reason for the problem is that you have Excel adjusting the relative references when do a copy + paste.

Suppose you have this formula in any cell in row A1480 ; it works well. Now , when you copy & paste this formula in some earlier row , as you have done with your posted formula , the references are all adjusted , so that A1480 becomes A1418 ; Excel is confused because A1 cannot be adjusted backwards by 62 rows ( 1480 - 1418 ).

If you always want the reference to be A1 , make it absolute so that Excel will not try to adjust it ; make it $A$1 or even A$1 if you intend to copy it up or down ; if you intend to copy it across , make it $A$1 or even $A1.

Narayan
 
Narayan is spot on for the reason. For the CELL function that we're using, it doesn't matter which cell we're referring to, but to make sure it doesn't cause problems, we can make the formula:
=HYPERLINK(TRIM(LEFT(SUBSTITUTE(CELL("filename",$A$1),"[",REPT(" ",999)),999))&"photos\"&A1480&".pdf","photos\"&$A1480&".pdf")
so that the A1 reference won't try to get shifted off of the sheet.
 
Luke M said:
Narayan is spot on for the reason. For the CELL function that we're using, it doesn't matter which cell we're referring to, but to make sure it doesn't cause problems, we can make the formula:
=HYPERLINK(TRIM(LEFT(SUBSTITUTE(CELL("filename",$A$1),"[",REPT(" ",999)),999))&"photos\"&A1480&".pdf","photos\"&$A1480&".pdf")
so that the A1 reference won't try to get shifted off of the sheet.
Click to expand...

Thanks guys, that's exactly what I wanted - it works perfectly, so from that perspective I'm delighted with your help.

I wonder if I dare ask for a little bit more, just to educate me? I tried looking up your response in my "Excel for Dummies 2007" book but I cannot find some of the functions (if this is the right word) and I cannot work out exactly what is going on. I hate to keep asking dumb questions but if you could spare a minute or two to educate me on what is going on within the Hyperlink example I'd be very grateful and maybe a bit more self-sufficient in future?
 
Sure thing. The functions I used in my formula:
HYPERLINK
TRIM
LEFT
SUBSTITUTE
CELL
REPT

Hyperlink - As you were already using this, won't go too much in depth. Let's you pass an address/link or sorts as a string, and gets converted to a hyperlink. The 2nd option argument is to give the "friendly" name, which is what the cell will display.

TRIM - Removes excess spaces from beginning/end of a string. E.g
=TRIM(" tom ")
produces
"tom"

LEFT - Extracts x number of characters from a string, starting on left. So, the formula:
=LEFT("Hello World!",5)
says to get the first 5 characters, and will return:
Hello

SUBSTITUTE - Give starting string, what string you want to search for, what to replace item with. Optional 4th argument lets you choose a specific instance of string, if desired. Example:
=SUBSTITUTE("Hello World!","World","Luke")
would produce:
Hello Luke!

CELL - Let's you query several different things about the environment of a cell. As they vary in what they do, I suggest looking up the XL help guide, and it will list full list. For us, I used the "filename" information, which gives full path of workbook and sheet name. Note that workbook must be saved already for this to work.
Example:
=CELL("filename",A1)
C:\My documents\[My book.xls]Sheet1

REPT - Let's you give a string, and then repeat it x number of times.
Example:
=REPT("A",3)
produces:
AAA

How I put it together:
The key info we want is the file path, but we don't need the workbook name or sheet. So, when we use the CELL function, we have what we need, but some extra stuff is on the end
C:\Some path\[Book name]Sheet1

to get rid of the extra part, need to find out where the "[" is. We could count where it is with SEARCH function, and then use the LEFT function on that string, but that would require using CELL function twice,:( and a bit more work. So instead, we using a cutting trick.:cool: By SUBSTITUTing the "[" with the repeated space symbol, we get something like:
Code: 
C:\Some path\       <lots of space>      Book name]Sheet1
(side note: tagging this as code so spacing is preserved)
Then, I can tell LEFT to just grab the first 999 chracters. Since I know I inserted 999 spaces, this will grab the part I want and a bunch of spaces, but not the trailing workbook name
Code: 
"C:\Some path\                          "

Then, the TRIM function removes those extra spaces
C:\Some path\
and we're ready to build the actual string we want and create our hyperlink.
 
Last edited:
Luke M said:
Sure thing. The function I used in my formula:
HYPERLINK
TRIM
LEFT
SUBSTITUTE
CELL
REPT

Hyperlink - As you were already using this, won't go too much in depth. Let's you pass an address/link or sorts as a string, and gets converted to a hyperlink. The 2nd option argument is to give the "friendly" name, which is what the cell will display.

TRIM - Removes excess spaces from beginning/end of a string. E.g., converts " tom " into "tom"

LEFT - Extracts x number of characters from a string, starting on left. So, the formula:
=LEFT("Hello World!",5)
says to get the first 5 characters, and will return:
Hello

SUBSTITUTE - Give starting string, what string you want to search for, what to replace item with. Optional 4th argument lets you choose a specific instance of string, if desired. Example:
=SUBSTITUTE("Hello World!","World","Luke")
would produce:
Hello Luke!

CELL - Let's you query several different things about the environment of a cell. As they vary in what they do, I suggest looking up the XL help guide, and it will list full list. For us, I used the "filename" information, which gives full path of workbook and sheet name. Note that workbook must be saved already for this to work.
Example:
=CELL("filename",A1)
C:\My documents\[My book.xls]Sheet1

REPT - Let's you give a string, and then repeat it x number of times.
Example:
=REPT("A",3)
produces:
AAA

How I put it together:
The key info we want is the file path, but we don't need the workbook name or sheet. So, when we use the CELL function, we have what we need, but some extra stuff is on the end
C:\Some path\[Book name]Sheet1

to get rid of the extra part, need to find out where the "[" is. We could count where it is with SEARCH function, and then use the LEFT function on that string, but that would require using CELL function twice,:( and a bit more work. So instead, we using a cutting trick.:cool: By SUBSTITUTing the "[" with the repeated space symbol, we get something like:
Code: 
C:\Some path\       <lots of space>      Book name]Sheet1
(side note: tagging this as code so spacing is preserved)
Then, I can tell LEFT to just grab the first 999 chracters. Since I know I inserted 999 spaces, this will grab the part I want and a bunch of spaces, but not the trailing workbook name
Code: 
"C:\Some path\                          "

Then, the TRIM function removes those extra spaces
C:\Some path\
and we're ready to build the actual string we want and create our hyperlink.
Click to expand...

That's fantastic - I've read it through once and it makes sense, but I can see I need to print it off and spend a bit of time studying it so that it seeps through into my dim brain and I can build on it later. Many, many thanks - very much appreciated.
 
You must log in or register to reply here.
Back
Top