Can Excel auto-fill series such as 1A, 1B, 1C, 2A, 2B, 2C, etc.?

Q

Quality101

I have a need to use excel to track large quantities of welds on construction
sites. These welds are identified by component and are numerically tracked
by row and sequence (i.e., 1A, 1B, 1C, 1D....1J; 2A, 2B, 2C, 2D....2J; etc).
There could most likely be 180-225 rows like this in a single component. So
far, I have been unable to use Excel's auto-fill capability to prevent from
having to enter each column individually. Any suggestions?
 
N

Nick Hodge

You will need to go through the pain once to set up a custom list. Enter
the data in a list and then go to tools>options>custom lists, enter the
range in the 'Import list from cells' box and click import...now it will
autofill like any other list Jan, Feb, Mar, etc.

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
(e-mail address removed)
 
B

Bob Phillips

For what you want, not possible I think. No matter how big you make a custom
list, you will not automatically get the next in series.

Here is a different approach.

Type 1A in A1

Select cell A2 and then Insert>Name>Define...
give it a name of GET_LETTER
with a Refersto value of
=MID($A1,MIN(IF(ISERROR(1*(MID($A1,ROW(INDIRECT("A1:A"&LEN($A1))),1))),
ROW(INDIRECT("A1:A"&LEN($A1))),255)),99)
OK out

In A2 add

=MID($A1,MIN(IF(ISERROR(1*(MID($A1,ROW(INDIRECT("A1:A"&LEN($A1))),1))),
ROW(INDIRECT("A1:A"&LEN($A1))),255)),99)

and copy A2 down as far as you want

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
B

Bob Phillips

Sorry, the second formula should be

=IF(GET_LETTER="J",SUBSTITUTE(A1,GET_LETTER,"")+1&"A",SUBSTITUTE(A1,GET_LETT
ER,"")&CHAR(CODE(GET_LETTER)+1))

that is the one in A2 and copied down.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top