Column reference
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 |
Column reference
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") "manuel" wrote: 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 |
Column reference
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 "Joel" wrote: 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") "manuel" wrote: 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 |
All times are GMT +1. The time now is 01:01 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com