Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to reference one column against another | Excel Worksheet Functions | |||
Column reference and column count | Excel Discussion (Misc queries) | |||
Row reference increment but preserve column reference | Excel Worksheet Functions | |||
Macro to Reference Column Next to Current Reference | Excel Discussion (Misc queries) | |||
I need to find the Average from Column A - but Reference Column B | Excel Worksheet Functions |