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.

******** ******************** ************************************************************************>

Microsoft Excel - Book3 ___Running: 11.0 : OS = Windows XP
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
B3C3D3 =
	A	B	C	D	E	F	G
1
2		Fld1	Fld2	Fld3		RowNumber	52
3		0	0	0
Sheet1

[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

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.