fill a series keeping the last number constant

[cyndi]

Hi I am using Excel 2003, Here’s the problem, I need to fill a series keeping
the last number constant: For Example
2566-1
2567-1
2568-1
2569-1
2570-1
Etc…

But I get this instead when I try to autofill it
2566-1
2567-1
2568-1
2566-2
2567-2
2568-2
Is there a way to keep the last number consitant? Any Help would be
awesome. Thanks

 

[TRYING]

Based on my limited knowledge, here's what I'd do. The regulars would
probably have a more efficient way of accomplishing this.

Assuming the first 4 digits keep incrementing by 1 and you want this series
in column A:

-Type 2566 and 2567 in cells A1 and A2.
-Select both cells and drag the autofill handle down as far as needed.
-Use a helper column, say column B (or any other convenient column) and type
this formula in cell B1: =A1&"-1"
-Copy this formula down as far as needed.
-Select all cells in column B with formula, copy in place as values.
-Move the contents of B1 to B . . . into A1 to A . . .

 

[Gord Dibben]

One method..........

Enter this in A1 and drag down

=25&ROW()+65 & "-1"

Copy and paste special>values>ok>esc.


Gord Dibben MS Excel MVP

 

[Tyro]

You could fill the cells below the first one (in A1 for example) with
=LEFT(A1,4)+1&"-1" and drag the formula down as far as you need.

Tyro

 

[TRYING]

GORD: Thanks. Even though I wasn't the original poster. I could use the
concept.

However, when I tried to extend the series past 2599-1, I got 25100-1, 25101-1

 

[Dave Peterson]

How about this in A1:
=2565+ROW()&"-1"

GORD: Thanks. Even though I wasn't the original poster. I could use the
concept.

However, when I tried to extend the series past 2599-1, I got 25100-1, 25101-1

 

[TRYING]

That should work for Cindy. It would take her a while before the first 4
digits become 5. By then she can easily edit the formula to fit the new
situation.

 

[cyndi]

Thanks Everyone, these are all helpful

You could fill the cells below the first one (in A1 for example) with
=LEFT(A1,4)+1&"-1" and drag the formula down as far as you need.

Tyro

 

[Ron Coderre]

If it's just for appearance...

Perhaps this:
Starter cell: 2566

From the Excel Main Menu:
<format><cells><number tab>
Category: Custom
Type: 0000-1
Click [OK]

(the cell will now display as: 2566-1)

Then, click an hold on the cell's fill handle
(small black box in the lower right)
and drag down while holding down the [CTRL] key.

The end result will look like this:
2566-1
2567-1
2568-1
2569-1
2570-1
etc

Is that something you can work with?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[JMart]

I have a question similar to this one. I'm trying to continue a series in
this way: ME-000-10, ME-001-10, ME-002-10, etc., but am stumped as to
how-thanks for any ideas!

If it's just for appearance...

Perhaps this:
Starter cell: 2566

From the Excel Main Menu:
<format><cells><number tab>
Category: Custom
Type: 0000-1
Click [OK]

(the cell will now display as: 2566-1)

Then, click an hold on the cell's fill handle
(small black box in the lower right)
and drag down while holding down the [CTRL] key.

The end result will look like this:
2566-1
2567-1
2568-1
2569-1
2570-1
etc

Is that something you can work with?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

Hi I am using Excel 2003, Here's the problem, I need to fill a series
keeping
the last number constant: For Example
2566-1
2567-1
2568-1
2569-1
2570-1
Etc.

But I get this instead when I try to autofill it
2566-1
2567-1
2568-1
2566-2
2567-2
2568-2
Is there a way to keep the last number consitant? Any Help would be
awesome. Thanks

 

[L. Howard Kittle]

Hi JMart,

try this.

="ME-0"&ROW()-1&-"10"

and pull down.
HTH
Regards,
Howard

I have a question similar to this one. I'm trying to continue a series in
this way: ME-000-10, ME-001-10, ME-002-10, etc., but am stumped as to
how-thanks for any ideas!

If it's just for appearance...

Perhaps this:
Starter cell: 2566

From the Excel Main Menu:
<format><cells><number tab>
Category: Custom
Type: 0000-1
Click [OK]

(the cell will now display as: 2566-1)

Then, click an hold on the cell's fill handle
(small black box in the lower right)
and drag down while holding down the [CTRL] key.

The end result will look like this:
2566-1
2567-1
2568-1
2569-1
2570-1
etc

Is that something you can work with?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

Hi I am using Excel 2003, Here's the problem, I need to fill a series
keeping
the last number constant: For Example
2566-1
2567-1
2568-1
2569-1
2570-1
Etc.

But I get this instead when I try to autofill it
2566-1
2567-1
2568-1
2566-2
2567-2
2568-2
Is there a way to keep the last number consitant? Any Help would be
awesome. Thanks

 

[Demccaff]

Howard,

Thank you! This is a great tool! Is there a way to accomplish this:

611-a1-a001-a; 611-a1-a002-a; 611-a1-a003-a; . . . 611-a1-a125-a, etc

I used your example to create this ="611-a1-a"&ROW()-4&"-a"

where I'm subtracting 4 from the row, as the series starts on row 5. But
the part I cannot get to work is the leading "0"s. Is there a way to have
two or one leading zero for a constant 3 characters?
Any help would be much appreciated.

Thank you,
David

Hi JMart,

try this.

="ME-0"&ROW()-1&-"10"

and pull down.
HTH
Regards,
Howard

I have a question similar to this one. I'm trying to continue a series in
this way: ME-000-10, ME-001-10, ME-002-10, etc., but am stumped as to
how-thanks for any ideas!

If it's just for appearance...

Perhaps this:
Starter cell: 2566

From the Excel Main Menu:
<format><cells><number tab>
Category: Custom
Type: 0000-1
Click [OK]

(the cell will now display as: 2566-1)

Then, click an hold on the cell's fill handle
(small black box in the lower right)
and drag down while holding down the [CTRL] key.

The end result will look like this:
2566-1
2567-1
2568-1
2569-1
2570-1
etc

Is that something you can work with?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

Hi I am using Excel 2003, Here's the problem, I need to fill a series
keeping
the last number constant: For Example
2566-1
2567-1
2568-1
2569-1
2570-1
Etc.

But I get this instead when I try to autofill it
2566-1
2567-1
2568-1
2566-2
2567-2
2568-2
Is there a way to keep the last number consitant? Any Help would be
awesome. Thanks

 

[Bob Umlas]

="611-a1-a"&text(ROW()-4,"000")&"-a"

Howard,

Thank you! This is a great tool! Is there a way to accomplish this:

611-a1-a001-a; 611-a1-a002-a; 611-a1-a003-a; . . . 611-a1-a125-a, etc

I used your example to create this ="611-a1-a"&ROW()-4&"-a"

where I'm subtracting 4 from the row, as the series starts on row 5. But
the part I cannot get to work is the leading "0"s. Is there a way to have
two or one leading zero for a constant 3 characters?
Any help would be much appreciated.

Thank you,
David

Hi JMart,

try this.

="ME-0"&ROW()-1&-"10"

and pull down.
HTH
Regards,
Howard

I have a question similar to this one. I'm trying to continue a series
in
this way: ME-000-10, ME-001-10, ME-002-10, etc., but am stumped as to
how-thanks for any ideas!

:

If it's just for appearance...

Perhaps this:
Starter cell: 2566

From the Excel Main Menu:
<format><cells><number tab>
Category: Custom
Type: 0000-1
Click [OK]

(the cell will now display as: 2566-1)

Then, click an hold on the cell's fill handle
(small black box in the lower right)
and drag down while holding down the [CTRL] key.

The end result will look like this:
2566-1
2567-1
2568-1
2569-1
2570-1
etc

Is that something you can work with?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

Hi I am using Excel 2003, Here's the problem, I need to fill a
series
keeping
the last number constant: For Example
2566-1
2567-1
2568-1
2569-1
2570-1
Etc.

But I get this instead when I try to autofill it
2566-1
2567-1
2568-1
2566-2
2567-2
2568-2
Is there a way to keep the last number consitant? Any Help would be
awesome. Thanks

 

[Bernie Deitrick]

Demccaff,

="611-a1-a"&TEXT(ROW()-4,"000")&"-a"

HTH,
Bernie
MS Excel MVP

Howard,

Thank you! This is a great tool! Is there a way to accomplish this:

611-a1-a001-a; 611-a1-a002-a; 611-a1-a003-a; . . . 611-a1-a125-a, etc

I used your example to create this ="611-a1-a"&ROW()-4&"-a"

where I'm subtracting 4 from the row, as the series starts on row 5. But
the part I cannot get to work is the leading "0"s. Is there a way to have
two or one leading zero for a constant 3 characters?
Any help would be much appreciated.

Thank you,
David

Hi JMart,

try this.

="ME-0"&ROW()-1&-"10"

and pull down.
HTH
Regards,
Howard

I have a question similar to this one. I'm trying to continue a series in
this way: ME-000-10, ME-001-10, ME-002-10, etc., but am stumped as to
how-thanks for any ideas!

:

If it's just for appearance...

Perhaps this:
Starter cell: 2566

From the Excel Main Menu:
<format><cells><number tab>
Category: Custom
Type: 0000-1
Click [OK]

(the cell will now display as: 2566-1)

Then, click an hold on the cell's fill handle
(small black box in the lower right)
and drag down while holding down the [CTRL] key.

The end result will look like this:
2566-1
2567-1
2568-1
2569-1
2570-1
etc

Is that something you can work with?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

Hi I am using Excel 2003, Here's the problem, I need to fill a series
keeping
the last number constant: For Example
2566-1
2567-1
2568-1
2569-1
2570-1
Etc.

But I get this instead when I try to autofill it
2566-1
2567-1
2568-1
2566-2
2567-2
2568-2
Is there a way to keep the last number consitant? Any Help would be
awesome. Thanks

 

[Demccaff]

Bernie,

You're Awesome!!!
That was sooooo fast too!!

Thank You!!!!

David

Demccaff,

="611-a1-a"&TEXT(ROW()-4,"000")&"-a"

HTH,
Bernie
MS Excel MVP

Howard,

Thank you! This is a great tool! Is there a way to accomplish this:

611-a1-a001-a; 611-a1-a002-a; 611-a1-a003-a; . . . 611-a1-a125-a, etc

I used your example to create this ="611-a1-a"&ROW()-4&"-a"

where I'm subtracting 4 from the row, as the series starts on row 5. But
the part I cannot get to work is the leading "0"s. Is there a way to have
two or one leading zero for a constant 3 characters?
Any help would be much appreciated.

Thank you,
David

Hi JMart,

try this.

="ME-0"&ROW()-1&-"10"

and pull down.
HTH
Regards,
Howard


I have a question similar to this one. I'm trying to continue a series in
this way: ME-000-10, ME-001-10, ME-002-10, etc., but am stumped as to
how-thanks for any ideas!

:

If it's just for appearance...

Perhaps this:
Starter cell: 2566

From the Excel Main Menu:
<format><cells><number tab>
Category: Custom
Type: 0000-1
Click [OK]

(the cell will now display as: 2566-1)

Then, click an hold on the cell's fill handle
(small black box in the lower right)
and drag down while holding down the [CTRL] key.

The end result will look like this:
2566-1
2567-1
2568-1
2569-1
2570-1
etc

Is that something you can work with?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

Hi I am using Excel 2003, Here's the problem, I need to fill a series
keeping
the last number constant: For Example
2566-1
2567-1
2568-1
2569-1
2570-1
Etc.

But I get this instead when I try to autofill it
2566-1
2567-1
2568-1
2566-2
2567-2
2568-2
Is there a way to keep the last number consitant? Any Help would be
awesome. Thanks