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.
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,343
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,343
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,500
Messages
5,832,072
Members
430,110
Latest member
Chyke_mxl

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top