Find Column letter to use in a formula

[a7n9]

Hello all,

I want to find the column letter to use in a formula. It would be clear when you look at this sheet.

Book3
	A	B	C	D	E	F	G
1
2		Fld1	Fld2	Fld3		RowNumber	52
3		0	0	0
Sheet1

As you can see in B3:D3 I have a formula, which uses a row number from cell G2, but as a column letter I've manually entered it as B, C and D. Is there a way to elminiate this manual portion and write a formula, which would find the column letter too? I've many fields like these, therefore I want a formula, which I can drag.

Thanks for your help.

 

[Norie]

Why not try OFFSET instead of INDIRECT?

=OFFSET(Sheet2!B3,$G$2-ROW(),0)

 

[NBVC]

One way:

=INDIRECT("Sheet2!"&LEFT(ADDRESS(1,COLUMN(),4),1)&$G$2)

 

[just_jon]

=INDEX(Sheet2!$1:$65536,$G$2,COLUMN())

You want to avoid a bunch of Indirects if you can.

EDIT to anchor the refs.

 

[Oorang]

Another way
=VLOOKUP(Sheet2!A3,Sheet2!3:3,Sheet1!G2,0))

 

[a7n9]

Thanks everyone.

just_jon: Very simple solution, thanks. (why didn't I think of that)
NBVC: It works, thanks. Now, I know how to find a column letter.
Norie: You're the boss, however, I didn't understand why are you substracting the current row?

 

[Norie]

Because we're using OFFSET.

The formula is in row 3 so if we offset by 52 rows we end up in row 55 on the other sheet, therefore if we subtract the row number of the cell we get the required 52.