Find Column letter to use in a formula

a7n9

Well-known Member
Joined
Sep 15, 2004
Messages
696
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
ABCDEFG
1
2Fld1Fld2Fld3RowNumber52
3000
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.
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,062
Office Version
  1. 365
Platform
  1. Windows
Why not try OFFSET instead of INDIRECT?

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

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
One way:

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

just_jon

Legend
Joined
Sep 3, 2002
Messages
10,473
=INDEX(Sheet2!$1:$65536,$G$2,COLUMN())

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

:eek: EDIT to anchor the refs.
 

Oorang

Well-known Member
Joined
Mar 4, 2005
Messages
2,071

ADVERTISEMENT

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

a7n9

Well-known Member
Joined
Sep 15, 2004
Messages
696
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,062
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,910
Messages
5,574,987
Members
412,632
Latest member
robertmwaring2
Top