fill a series keeping the last number constant
 

Hi I am using Excel 2003, Heres 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

fill a series keeping the last number constant
 

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 . . .



"cyndi" wrote:

Hi I am using Excel 2003, Heres 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

fill a series keeping the last number constant
 

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

Enter this in A1 and drag down

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

Copy and paste specialvaluesokesc.


Gord Dibben MS Excel MVP


On Wed, 9 Jan 2008 14:00:07 -0800, cyndi
wrote:

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

fill a series keeping the last number constant
 

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

"cyndi" wrote in message
...
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

fill a series keeping the last number constant
 

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

"Gord Dibben" wrote:

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

Enter this in A1 and drag down

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

Copy and paste specialvaluesokesc.


Gord Dibben MS Excel MVP


On Wed, 9 Jan 2008 14:00:07 -0800, cyndi
wrote:

Hi I am using Excel 2003, Heres 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

fill a series keeping the last number constant
 

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

TRYING wrote:

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

"Gord Dibben" wrote:

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

Enter this in A1 and drag down

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

Copy and paste specialvaluesokesc.


Gord Dibben MS Excel MVP


On Wed, 9 Jan 2008 14:00:07 -0800, cyndi
wrote:

Hi I am using Excel 2003, Heres 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




--

Dave Peterson

fill a series keeping the last number constant
 

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.

"Dave Peterson" wrote:

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

TRYING wrote:

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

"Gord Dibben" wrote:

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

Enter this in A1 and drag down

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

Copy and paste specialvaluesokesc.


Gord Dibben MS Excel MVP


On Wed, 9 Jan 2008 14:00:07 -0800, cyndi
wrote:

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




--

Dave Peterson

fill a series keeping the last number constant
 

Thanks Everyone, these are all helpful :)

"Tyro" wrote:

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

"cyndi" wrote in message
...
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

fill a series keeping the last number constant
 

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)

"cyndi" wrote in message
...
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

fill a series keeping the last number constant
 

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!

"Ron Coderre" wrote:

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)

"cyndi" wrote in message
...
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

fill a series keeping the last number constant
 

Hi JMart,

try this.

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

and pull down.
HTH
Regards,
Howard


"JMart" wrote in message
...
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!

"Ron Coderre" wrote:

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)

"cyndi" wrote in message
...
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

fill a series keeping the last number constant
 

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

"L. Howard Kittle" wrote:

Hi JMart,

try this.

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

and pull down.
HTH
Regards,
Howard


"JMart" wrote in message
...
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!

"Ron Coderre" wrote:

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)

"cyndi" wrote in message
...
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

fill a series keeping the last number constant
 

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

"Demccaff" wrote in message
...
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

"L. Howard Kittle" wrote:

Hi JMart,

try this.

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

and pull down.
HTH
Regards,
Howard


"JMart" wrote in message
...
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!

"Ron Coderre" wrote:

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)

"cyndi" wrote in message
...
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

fill a series keeping the last number constant
 

Demccaff,

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

HTH,
Bernie
MS Excel MVP


"Demccaff" wrote in message
...
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

"L. Howard Kittle" wrote:

Hi JMart,

try this.

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

and pull down.
HTH
Regards,
Howard


"JMart" wrote in message
...
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!

"Ron Coderre" wrote:

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)

"cyndi" wrote in message
...
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

fill a series keeping the last number constant
 

Bernie,

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

Thank You!!!!

David

"Bernie Deitrick" wrote:

Demccaff,

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

HTH,
Bernie
MS Excel MVP


"Demccaff" wrote in message
...
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

"L. Howard Kittle" wrote:

Hi JMart,

try this.

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

and pull down.
HTH
Regards,
Howard


"JMart" wrote in message
...
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!

"Ron Coderre" wrote:

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)

"cyndi" wrote in message
...
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