1, 2, 3, 4, ... 1a, 1b, 1c, ...

[mkengel]

By dragging the black rectangle at the bottom of a cell, you can
automatically increase a number by plus one while moving from one cell
to the next..
Is it possible in Excel to start, e.g. with 1a, and by dragging, you
can create new cells with the content 1b, 1c, 1d, ..., 1z ?

Thank you for any help.
Michael

[VBA Noob]

You could try Tools Options and Custom lists if this is something you do regularly

otherwise you could put A in Cell A1 and B in cell B1 then use concatenate to join them in cell C1.

e.g =A1&B1

VBA Noob

[Leo Heuser]

"mkengel" <[email protected]> skrev i en meddelelse
news:[email protected]...
> By dragging the black rectangle at the bottom of a cell, you can
> automatically increase a number by plus one while moving from one cell
> to the next..
> Is it possible in Excel to start, e.g. with 1a, and by dragging, you
> can create new cells with the content 1b, 1c, 1d, ..., 1z ?
>
> Thank you for any help.
> Michael
>

Hi Michael

One way:

Rowwise.
In A1:
=1&CHAR(97+ROW()-ROW($A$1))

Copy A1 down with the fill handle.

If you start in e.g. K3:
=1&CHAR(97+ROW()-ROW($K$3))


Columnwise.
In A1:
=1&CHAR(97+COLUMN()-COLUMN($A$1))

Copy A1 to the right.

If you start in e.g. K3:
=1&CHAR(97+COLUMN()-COLUMN($K$3))


--
Best regards
Leo Heuser

Followup to newsgroup only please.

[VBA Noob]

or

=IF(ISERROR(COLUMN()&CHOOSE(MOD(ROW(),27),"a","b","c","d","e","f","g","h","i","j","k","l","m","n","o","p","q","r","s","t","u","v","w","x","y","z")),"Next Row",COLUMN()&CHOOSE(MOD(ROW(),27),"a","b","c","d","e","f","g","h","i","j","k","l","m","n","o","p","q","r","s","t","u","v","w","x","y","z"))

[VBA Noob]

or

=COLUMN($A$1)&CHOOSE(IF((MOD(ROW(),26))=0,26,((MOD(ROW(),26)))),"a","b","c","d","e","f","g","h","i","j","k","l","m","n","o","p","q","r","s","t","u","v","w","x","y","z")

[Gord Dibben]

If you want to go past 1Z try this formula.

=1&LOWER(SUBSTITUTE(ADDRESS(1,ROW(A1),4),"1",""))

Will take you to 1iv

Don't remember who came up with this one, just passing along.


Gord Dibben MS Excel MVP


On Thu, 27 Jul 2006 10:40:09 +0200, "Leo Heuser"
<[email protected]> wrote:

>"mkengel" <[email protected]> skrev i en meddelelse
>news:[email protected]...
>> By dragging the black rectangle at the bottom of a cell, you can
>> automatically increase a number by plus one while moving from one cell
>> to the next..
>> Is it possible in Excel to start, e.g. with 1a, and by dragging, you
>> can create new cells with the content 1b, 1c, 1d, ..., 1z ?
>>
>> Thank you for any help.
>> Michael
>>
>
>Hi Michael
>
>One way:
>
>Rowwise.
>In A1:
>=1&CHAR(97+ROW()-ROW($A$1))
>
>Copy A1 down with the fill handle.
>
>If you start in e.g. K3:
>=1&CHAR(97+ROW()-ROW($K$3))
>
>
>Columnwise.
>In A1:
>=1&CHAR(97+COLUMN()-COLUMN($A$1))
>
>Copy A1 to the right.
>
>If you start in e.g. K3:
>=1&CHAR(97+COLUMN()-COLUMN($K$3))

[Gord Dibben]

If you want to go past 1Z try this formula.

=1&LOWER(SUBSTITUTE(ADDRESS(1,ROW(A1),4),"1",""))

Will take you to 1iv

Don't remember who came up with this one, just passing along.


Gord Dibben MS Excel MVP


On Thu, 27 Jul 2006 10:40:09 +0200, "Leo Heuser"
<[email protected]> wrote:

>"mkengel" <[email protected]> skrev i en meddelelse
>news:[email protected]...
>> By dragging the black rectangle at the bottom of a cell, you can
>> automatically increase a number by plus one while moving from one cell
>> to the next..
>> Is it possible in Excel to start, e.g. with 1a, and by dragging, you
>> can create new cells with the content 1b, 1c, 1d, ..., 1z ?
>>
>> Thank you for any help.
>> Michael
>>
>
>Hi Michael
>
>One way:
>
>Rowwise.
>In A1:
>=1&CHAR(97+ROW()-ROW($A$1))
>
>Copy A1 down with the fill handle.
>
>If you start in e.g. K3:
>=1&CHAR(97+ROW()-ROW($K$3))
>
>
>Columnwise.
>In A1:
>=1&CHAR(97+COLUMN()-COLUMN($A$1))
>
>Copy A1 to the right.
>
>If you start in e.g. K3:
>=1&CHAR(97+COLUMN()-COLUMN($K$3))