nepotist
03-22-2011, 10:42 AM
Hello,
I am trying to retrieve the cell reference based on userinput. For example I have a row header Form 1990 to 2010 and next year we will introduce another column with 2011. I would like to update my results by changing year at one place instead of me changing the all the formulas.
There are more columns after the year data
say 1990 to 2010 followed by k, d, a..
I tried doing a Hlookup (lookup range being 1990 to a) in combination with address in one single row, but it gives me a value that makes no sense.
Is there a better way around?
At this moment I have tried this
=ADDRESS(1,HLOOKUP(BA1,A1:AR1,1,FALSE))
When I experiement it with a sample data I get the right address. But when I use the formula in my main data set it gives me $BYH$1 as a result, which doesnt make sense as the value is cell AP1.
If it worked correctly I would like to Use the Column "AP" and tweak my formulas. Can any one please point out the mistake in this.
Thank you
Edit:
The above formula is absolutely wrong my bad.
I am trying to retrieve the cell reference based on userinput. For example I have a row header Form 1990 to 2010 and next year we will introduce another column with 2011. I would like to update my results by changing year at one place instead of me changing the all the formulas.
There are more columns after the year data
say 1990 to 2010 followed by k, d, a..
I tried doing a Hlookup (lookup range being 1990 to a) in combination with address in one single row, but it gives me a value that makes no sense.
Is there a better way around?
At this moment I have tried this
=ADDRESS(1,HLOOKUP(BA1,A1:AR1,1,FALSE))
When I experiement it with a sample data I get the right address. But when I use the formula in my main data set it gives me $BYH$1 as a result, which doesnt make sense as the value is cell AP1.
If it worked correctly I would like to Use the Column "AP" and tweak my formulas. Can any one please point out the mistake in this.
Thank you
Edit:
The above formula is absolutely wrong my bad.