Find Column Letters

P

Pestomania

Active Member
Joined
May 30, 2018
Messages
332
Office Version
  1. 365
Platform
  1. Windows
I have searched through numerous threads but cannot get any of the formulas to work.

I am trying to get the lookup for column values for the following data. The other options is if we start with the first column letter (ex: AW), how can I add 11 to that to get BH? That might be an easier option.


Book1
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAWAXAYAZBABBBCBDBEBFBGBHBIBJBKBLBMBNBOBPBQBRBSBT
1'23'24'25'26'27'28'29'30'31'32'33'34'35'36'37'38'39'40'41'42'43
2
3
41/1/20232/1/20233/1/20234/1/20235/1/20236/1/20237/1/20238/1/20239/1/202310/1/202311/1/202312/1/20231/1/20242/1/20243/1/20244/1/20245/1/20246/1/20247/1/20248/1/20249/1/202410/1/202411/1/202412/1/20241/1/20252/1/20253/1/20254/1/20255/1/20256/1/20257/1/20258/1/20259/1/202510/1/202511/1/202512/1/20251/1/20262/1/20263/1/20264/1/20265/1/20266/1/20267/1/20268/1/20269/1/202610/1/202611/1/202612/1/20261/1/20272/1/20273/1/20274/1/20275/1/20276/1/20277/1/20278/1/20279/1/202710/1/202711/1/202712/1/20271/1/20282/1/20283/1/20284/1/20285/1/20286/1/20287/1/20288/1/20289/1/202810/1/202811/1/202812/1/2028
5
6
7Start of YearEnd of Year
8AL2023
9MX2024
102025
112026
122027
132028
142029
152030
162031
172032
182033
192034
202035
212036
222037
232038
242039
252040
262041
272042
282043
Sheet1
Cell Formulas
RangeFormula
A1A1="'"&23
B1:U1B1="'"&RIGHT(A1,2)+1
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Sort by date Sort by votes
Why do you need column letters?
 
Upvote 0
RoryA said:
Why do you need column letters?
Click to expand...
This formula was written by my boss in an old file that I am trying to update and make a little more dynamic. All of these formulas use a "lookup" for year end and year start and row numbers. I am trying to clean these up a little bit. The Row # unfortunately cannot be automated that I can figure out at this time either.

'23'24'25'26'27'28'29'30'31'32'33'34'35'36'37'38'39'40'41'42'43TotalRowYear startYear End
0000000000000000000000174AYBJ2023
000000000000000000000094BKBV2024
0000000000000000000000466BWCH2025
2325.8160.40120.3-40.1-280.7160.4-80.280.2-80.280.22326160.40120.3-40.1-281160-80.280.2-80.22446433CICT2026
134201000-100.500000134201000-1010000235225CUDF2027
3900000000000390000000000390277DGDR2028
0000000000000000000000329DSED2029
601.50000000000602000000000602393EEEP2030
EQFB2031
0000000000000000000000599EEEP2032
2400000000000240000000000240638EQFB2033
00000000000000000000006862034
2.1500000000002.150000000002.155232035
00000000000000000000005632036
00000000000000000000007362037
00000000000000000000007862038
00000000000000000000008352039
24000000000024000000000248842040
00000000000000000000009282041
2042
2043
 
Upvote 0
You haven't posted the formulas, but I can't imagine why some combination of Index and Match wouldn't work. I haven't needed a column letter in 30+ years of Excel work so I'm always a little suspicious. ;)
 
Upvote 0
Here we go, sorry. Selected the wrong option:

10 Year Capacity Planning Model Rev 20231002.xlsm
BCDEFGHIJKLMNOPQRSTUVWXYZAAAB
198'23'24'25'26'27'28'29'30'31'32'33'34'35'36'37'38'39'40'41'42'43TotalRowYear startYear End
1990000000000000000000000174AYBJ2023
200000000000000000000000094BKBV2024
2010000000000000000000000466BWCH2025
2022325.8160.40120.3-40.1-280.7160.4-80.280.2-80.280.22326160.40120.3-40.1-281160-80.280.2-80.22446433CICT2026
203134201000-100.500000134201000-1010000235225CUDF2027
2043900000000000390000000000390277DGDR2028
2050000000000000000000000329DSED2029
206601.50000000000602000000000602393EEEP2030
207EQFB2031
2080000000000000000000000599EEEP2032
2092400000000000240000000000240638EQFB2033
21000000000000000000000006862034
2112.1500000000002.150000000002.155232035
21200000000000000000000005632036
21300000000000000000000007362037
21400000000000000000000007862038
21500000000000000000000008352039
21624000000000024000000000248842040
21700000000000000000000009282041
2182042
2192043
Dashboard
Cell Formulas
RangeFormula
B198B198="'"&23
C198:V198C198="'"&RIGHT(B198,2)+1
B199:B206,B208:B217B199=MAX(INDIRECT(CONCAT("'Capacity Charts'!",$Z$199,$Y199,":",$AA$199,$Y199)))
C199:C206,C208:C217C199=MAX(INDIRECT(CONCAT("'Capacity Charts'!",$Z$200,$Y199,":",$AA$200,$Y199)))-B199
D199:D206,D208:D217D199=MAX(INDIRECT(CONCAT("'Capacity Charts'!",$Z$201,$Y199,":",$AA$201,$Y199)))-(SUM(B199:C199))
E199:E206,E208:E217E199=MAX(INDIRECT(CONCAT("'Capacity Charts'!",$Z$202,$Y199,":",$AA$202,$Y199)))-SUM(B199:D199)
F199:F206,F208:F217F199=MAX(INDIRECT(CONCAT("'Capacity Charts'!",$Z$203,$Y199,":",$AA$203,$Y199)))-SUM(B199:E199)
G199:G206,G208:G217G199=MAX(INDIRECT(CONCAT("'Capacity Charts'!",$Z$204,$Y199,":",$AA$204,$Y199)))-SUM(B199:F199)
H199:H206,H208:H217H199=MAX(INDIRECT(CONCAT("'Capacity Charts'!",$Z$205,$Y199,":",$AA$205,$Y199)))-SUM(B199:G199)
I199:I206,I208:I217I199=MAX(INDIRECT(CONCAT("'Capacity Charts'!",$Z$206,$Y199,":",$AA$206,$Y199)))-SUM(B199:H199)
J199:J206,J208:J217J199=MAX(INDIRECT(CONCAT("'Capacity Charts'!",$Z$207,$Y199,":",$AA$207,$Y199)))-SUM(B199:I199)
K199:K206,K208:K217K199=MAX(INDIRECT(CONCAT("'Capacity Charts'!",$Z$208,$Y199,":",$AA$208,$Y199)))-SUM(B199:J199)
L199:V206,L208:V217L199=MAX(INDIRECT(CONCAT("'Capacity Charts'!",$Z$209,$Y199,":",$AA$209,$Y199)))-SUM(B199:K199)
X199:X206,X208:X217X199=SUM(B199:L199)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
X199:X206,X208:X219Cell Value>0textNO
B199:W206,B208:W219Cell Value>0textNO
 
Upvote 0
Pestomania said:
...

The other options is if we start with the first column letter (ex: AW), how can I add 11 to that to get BH? That might be an easier option.
Click to expand...

Excel Formula: 
=SUBSTITUTE(ADDRESS(1,( COLUMN(AW1) +11 ),4),1,"")
 
Upvote 0
johnnyL said:
Excel Formula: 
=SUBSTITUTE(ADDRESS(1,( COLUMN(AW1) +11 ),4),1,"")
Click to expand...
See below data. It worked for the first attempt and then failed.

10 Year Capacity Planning Model Rev 20231002.xlsm
ZAAAB
198Year startYear End
199AYAK2023
200ALAK2024
201ALAK2025
202ALAK2026
203ALAK2027
204ALAK2028
205ALAK2029
206ALAK2030
207ALAK2031
208ALAK2032
209ALAK2033
210ALAK2034
211ALAK2035
212ALAK2036
213ALAK2037
214ALAK2038
215ALAK2039
216ALAK2040
217ALAK2041
218ALAK2042
219ALAK2043
Dashboard
Cell Formulas
RangeFormula
AA199:AA219AA199=SUBSTITUTE(ADDRESS(1,( COLUMN(Z199) +11 ),4),1,"")
Z200,Z202:Z219Z200=SUBSTITUTE(ADDRESS(1,( COLUMN(Z199) +12 ),4),1,"")
Z201Z201=SUBSTITUTE(ADDRESS(1,( COLUMN(Z200)+12),4),1,"")
 
Upvote 0
See if this does what you ar trying to do:

Book1
YZAAAB
6
7Year StartYear End
8AL
9MX
10AWBH
11AKAV
12
Sheet3
Cell Formulas
RangeFormula
AA8:AA11AA8=SUBSTITUTE(ADDRESS(1, COLUMN(INDIRECT(Z8 & 1))+11, 4), 1, "")
 
Upvote 1
Solution
johnnyL said:
See if this does what you ar trying to do:

Book1
YZAAAB
6
7Year StartYear End
8AL
9MX
10AWBH
11AKAV
12
Sheet3
Cell Formulas
RangeFormula
AA8:AA11AA8=SUBSTITUTE(ADDRESS(1, COLUMN(INDIRECT(Z8 & 1))+11, 4), 1, "")
Click to expand...
This did great JohnnyL! Thank youfor your help and the table works now as originally intended. At some point in time, I'd like to make it based more in the table itself rather than running indirects, but time does not permit.
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,224,823
Messages
6,181,175
Members
453,021
Latest member
Justyna P

Share this page

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
Back
Top