Making the column letters dynamic inside a formula

[Daksh Agarwal]

Thanks in advance. I have this formula mentioned below. I want to make the column reference "DO" dynamic. I have a data set where I want it to pick the column number from (In this data set one column has Numbers & the other has column reference). I want to make this column reference to change based on an a hard coded input cell where i give a particular number which it can lookup in the above mentioned data set to get the column letters.

 

[awoohaw]

Hi, welcome to the forum. There is an add in if you can install, xl2bb (link below) you can share parts of your workbook that can help the forum users help you.
I'm not sure if youre asking to use an INDIRECT reference inside the OFFSET function. But, what you could do to calculate a the distance from that target cell and the current cell is to subtract the Col numbers of that cell and the row numbers of that cell to get offsets.
So, say your location of data cell is C5, and you are building the formula in D10.

=OFFSET(D10,ROW($C$5)-ROW(D10),COLUMN(C5)-COLUMN(D10))

I have no idea if this helps your solution or not:

Mr Excel 8.xlsm
	B	C	D	E
3
4
5		Hello
6
7
8
9
10			Hello
11
Sheet14

Cell Formulas
Range	Formula
D10	D10	=OFFSET(D10,ROW($C$5)-ROW(D10),COLUMN(C5)-COLUMN(D10))