Function for Cell Location

[psulions83]

Hi, I think I am looking at this the wrong way but is there a way for me to use a formula to use this to identify a location?

I am using a looking for example and I want my formula to pull the location of the cell for one of the parts of the formula. Ill paste formula below.

=VLOOKUP($A9,'Sheet 1'!$A:$BA,'Sheet 2'!$B$1,0)

I am using 'Sheet 2'!$B$1 to reference my column location on my lookup. I want to be able to change the B in that portion to be the location so I do not have to manually update.

Any help would be appreciated. Thanks!

 

[cerfani]

cut cell B1 on sheet 2 and paste it somewhere else, the formulas that reference the cut cell will update