Column reference

[Guest]

How can I find the column of a reference I have in a different worksheet?
For example in A1 I have ='D:\Work\[Vars.xls]Sheet1'!D$4 which makes
reference to column 4 in D:\Work\[Vars.xls]Sheet1. I would like to find that
column number without entering the address, since that might change. Or
associating it to A1 so that if I change A1 the column number would change
automatically.

Thank you,

Manuel

 

[Guest]

I don't know if this will help. but I wrote a neat function (only) one line
that will return the formula in an cell. You can then search the returned
string for any text you are looking for.

Function GetFormula(mycell As String) As String

GetFormula = Range(mycell).Formula
End Function


To use:

in cell A6 enter - make sure A%5 is entered as text.
=GetFormula("A5")

 

[Guest]

Thanks. It does return the string but I cannot embed it within a column()
function - column(GetFormula("A5")) - so I cannot get the formula for the
string back. It puts me a little closer to the solution but ....

Thanks anyways.

Manuel

I don't know if this will help. but I wrote a neat function (only) one line
that will return the formula in an cell. You can then search the returned
string for any text you are looking for.

Function GetFormula(mycell As String) As String

GetFormula = Range(mycell).Formula
End Function


To use:

in cell A6 enter - make sure A%5 is entered as text.
=GetFormula("A5")

How can I find the column of a reference I have in a different worksheet?
For example in A1 I have ='D:\Work\[Vars.xls]Sheet1'!D$4 which makes
reference to column 4 in D:\Work\[Vars.xls]Sheet1. I would like to find that
column number without entering the address, since that might change. Or
associating it to A1 so that if I change A1 the column number would change
automatically.

Thank you,

Manuel