Hyperlink to cell using formula result

[ratherbgolfing]

I am trying to use the following formula to hyperlink to a cell in another workbook.

I am looking up the cell I want to go to using
=ADDRESS(MATCH(A15,[LCIREDFLAGNOTES.xlsx]LCI!$A:$A,0),1,4,1)

Then I want to hyperlink to that cell in that workbook. I have tried alot of different stuff but the one I keep coming back to is

=HYPERLINK("L:\Reports\redflag\lciredflagnotes.xlsx"ADDRESS(MATCH(A15,[LCIREDFLAGNOTES.xlsx]LCI!$A:$A,0),1,4,1)

Which obviously doesn't work. I need to lookup the cell that matches a value and hyperlink to the corresponding cell in the other workbook. Any help would be great.

 

[Randall00]

I've found the HYPERLINK formula to be a pretty nasty and unforgiving way of linking to other workbooks dynamically. It doesn't really jive with other formulas in ways that are intuitive like the other functions.

It can be done in VBA if you're comfortable poking around in that territory, but it's contingent on making the source workbook macro-enabled.

 

[ratherbgolfing]

To be honest I have messed with VBA very little. I don't mind the macro enabled workbooks, But I would need some direction on completing the vba to accomplish what I'm trying to do.

 

[Randall00]

Well, the more variables, the more scary and complicated the VBA starts to get, but you might be able to get away with just a few lines, depending on...well, a few things:

-Does the file name or file path of the target workbook ever change?

-Is the cell that you want the hyperlink to appear in the exact same cell as the one with the ADDRESS formula?

If these are both "yes", this might not be too tough after all!

 

[ratherbgolfing]

The file name and path will never change and the cell with the address fomula will not change. The only thing that will change is the data in the reference cell for the address formula which should not affect anything.

I really appriciate your help!

 

[Randall00]

No worries, I'll give it a shot. (As long as it's not due in the next few hours!)

But just to confirm, the cell that you want the hyperlink is the same as the cell with the address formula, right? As in, you'll click on a cell that says "$A$1" and it should hyperlink you to A1 in the other workbook??

 

[ratherbgolfing]

That is exactly right!

 

[Randall00]

Aaaand just to be safe:

-Which column are the address formulas in?

-Ideally, would the hyperlink open the target workbook as well, or are they both open when you intend to use these links?

 

[ratherbgolfing]

The column with the address formula would be in column H and the first time they click the hyperlink the other workbook would be closed. If they clicked another hyperlink it would then be open

 

[ratherbgolfing]

Randall00 - Did you have any luck with this?