Incrementing/decrementing column characters using only worksheet functions?

D

Dan

How can I increment and decrement column characters/letters using
worksheet functions?

I have a list of 5 characters corresponding to columns.

The first character might be A, for column 1.

How can I get the second character in the list to automatically
configure itself to be B, the third C, the fourth D and the fifth E?

That is, I want to set this up so that the second character is linked
to the first, the third to the second and so on.

That way if I change the first character from A to D then the second
character in the list will automatically become E, the third will
change to F, the fourth G and the fifth H.

Of course, there could be two characters for an item in the list as
the column index could be anywhere between A and IV.

Moreover this needs to be done only with worksheet functions only as I
cannot guarantee that the sheet will have access to VBA.

I have been trying formulas that use a whole mess of CODE() and CHAR()
and so on, but I was wondering if anybody else has any ideas for an
elegant solution.

Any help would be most welcome.

Regards,
Dan
 
B

Bernie Deitrick

Dan,

For the column letter in cell A1, say, use this formula in cell B1 (or cell
A2) and copy down or accross as needed:

=SUBSTITUTE(ADDRESS(1,COLUMN(INDIRECT(A1& "1"))+1,4),"1","")

HTH,
Bernie
MS Excel MVP
 
D

Dan

Bernie Deitrick said:
Dan,

For the column letter in cell A1, say, use this formula in cell B1 (or cell
A2) and copy down or accross as needed:

=SUBSTITUTE(ADDRESS(1,COLUMN(INDIRECT(A1& "1"))+1,4),"1","")

HTH,
Bernie
MS Excel MVP
Click to expand...

Bernie - that's ingenious and a lot classier than my effort. Seems to
work well. Many thanks to you and also to Al for responding.

Dan
 
Top