First thing first, nice to meet you all in this forum, I am a new joiner to this forum and excel VBA.
Well, to make it short and simple, I am an audit associate, who need to indicate the reference where the figure in my working paper comes from.
So can you all help me to create some macro, or anything which I might not know, as long as it can serve the above purpose.
I have tried using hyperlink function, it works fine for the link within the workbook. But as long as I tried to make a link to external, especially closed workbook, it will have some problems.
Below is the code
I do not use any Defined name as it will make the workbook become full of name. The link, self and pair is the description only.
The code will be applied for both cells which I wish to link them. For example, lets say I want to link Sheet1!B1 to Sheet2!C2. Then I will enter the above formula to B1,B2,B3 and C2,C3,C4 respectively.
I didn't remove the "Pair" function as "cell("address") will not work for close workbook. If it can work for close workbook, maybe I can re-code the thing.
The above code work for both internal and external workbook link, but with the limitation that in order to get the link updated, the link workbook must be opened together.
I would be glad if anyone can solve this for me. And any other radical way are welcomed, because I am quite novice in VBA and excel.
Well, to make it short and simple, I am an audit associate, who need to indicate the reference where the figure in my working paper comes from.
So can you all help me to create some macro, or anything which I might not know, as long as it can serve the above purpose.
I have tried using hyperlink function, it works fine for the link within the workbook. But as long as I tried to make a link to external, especially closed workbook, it will have some problems.
Below is the code
Code:
[U]Link (The link)[/U]
=HYPERLINK(MID(B40,1,FIND("-Div-",B40,1)-1),MID(B40,FIND("-Div-",B40,1)+5,65536))
[U]Self (The path, filename, sheet name -Div- Ref of self)[/U]
="["&SUBSTITUTE((SUBSTITUTE(CELL("filename",B38),"[","",1)),"]","]'",1)&"'!"&CELL("address",B38)&"-Div-"&MID(CELL("filename",B38),FIND("]",CELL("filename",B38),1)+1,65536)
[U]Pair (The path, filename, sheet name -Div- Ref of the pair)[/U]
='A100'!H12I do not use any Defined name as it will make the workbook become full of name. The link, self and pair is the description only.
The code will be applied for both cells which I wish to link them. For example, lets say I want to link Sheet1!B1 to Sheet2!C2. Then I will enter the above formula to B1,B2,B3 and C2,C3,C4 respectively.
I didn't remove the "Pair" function as "cell("address") will not work for close workbook. If it can work for close workbook, maybe I can re-code the thing.
The above code work for both internal and external workbook link, but with the limitation that in order to get the link updated, the link workbook must be opened together.
I would be glad if anyone can solve this for me. And any other radical way are welcomed, because I am quite novice in VBA and excel.
