PDA

View Full Version : Solved: Cell Reference



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.

Bob Phillips
03-22-2011, 11:32 AM
Hrad to say without seeing the data, but maybe

=ADDRESS(1,HLOOKUP(BA1,$A$1:$AR$1,1,False))

nepotist
03-22-2011, 11:44 AM
Xld,

It wouldn't work that way, I have attached a sample excel sheet (No macros in it). I hope it would explain clearly what I am trying to achieve.
As always thank you!

Bob Phillips
03-22-2011, 02:17 PM
Try this

=ADDRESS(1,MATCH(N1,$A$1:$H$1,0))