Help with linking......

[qawhaq]

Is there a FORMULA which will tell what external reference a cell is linked to?

Say cell A1 on workbook1 is linked to cell A1 on workbook2: =[Book2]Sheet1!$A$1

In cell A2 on Workbook1, I want a formula which returns the name and location of the link, NOT the value. Cell A2 needs to return "[Book2]Sheet1!$A$1"

I need the return to be the file name so I can use it within INDIRECT form formulas elsewhere in workbook1.

Any thoughts?

 

[jscranton]

A UDF will get you there. There may be another way already cooked into Excel but this will work:

Function getFormula(rng As Range) As String
Dim Formula


Formula = CStr(rng.Formula)


getFormula = Replace(Formula, "=", "", 1)


End Function

 

[qawhaq]

Thanks, Not sure this is the way I need to go, but it's a fall back.

 

[NeonRedSharpie]

Thanks, Not sure this is the way I need to go, but it's a fall back.

You can't get the formula of a cell without a macro or a UDF. There is no way to pull the formula out of a cell.

 

[jscranton]

This means that you can either (1) use a UDF like the one I put in above or (2) add the range address a string somewhere in the worksheet so it can be referred to in your INDIRECT function.