Incrementing/decrementing column characters using only worksheet functions?

[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

 

[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

 

[Al]

So have you tried the simple formula
=CHAR(CODE(A1)+x)
where A1 would contain the starting value (e.g., "A") and
x would be how many you want to increment by.

For example if A1=A,
=CHAR(CODE(A1)+1) would return B

(This IS case sensitive.)

You could just use this formula for each value of x you
required, better yet using a cell reference for x.

 

[Bernie Deitrick]

Al,

That won't work when Dan enters "Z" and expects "AA" as the next entry.

HTH,
Bernie
MS Excel MVP

 

[Dan]

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

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