A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

fill a series keeping the last number constant



 
 
Thread Tools Display Modes
  #1  
Old January 9th, 2008, 11:00 PM posted to microsoft.public.excel.misc
cyndi
external usenet poster
 
Posts: 66
Default fill a series keeping the last number constant

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


  #2  
Old January 9th, 2008, 11:23 PM posted to microsoft.public.excel.misc
Trying
external usenet poster
 
Posts: 49
Default 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, 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


  #3  
Old January 9th, 2008, 11:40 PM posted to microsoft.public.excel.misc
Gord Dibben
external usenet poster
 
Posts: 20,252
Default 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


  #4  
Old January 9th, 2008, 11:52 PM posted to microsoft.public.excel.misc
Tyro[_2_]
external usenet poster
 
Posts: 1,104
Default 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




  #5  
Old January 10th, 2008, 12:00 AM posted to microsoft.public.excel.misc
Trying
external usenet poster
 
Posts: 49
Default 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, 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



  #6  
Old January 10th, 2008, 12:06 AM posted to microsoft.public.excel.misc
Dave Peterson
external usenet poster
 
Posts: 19,791
Default 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, 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
  #7  
Old January 10th, 2008, 12:24 AM posted to microsoft.public.excel.misc
Trying
external usenet poster
 
Posts: 49
Default 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

  #8  
Old January 10th, 2008, 12:42 AM posted to microsoft.public.excel.misc
cyndi
external usenet poster
 
Posts: 66
Default 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





  #9  
Old January 10th, 2008, 12:43 AM posted to microsoft.public.excel.misc
Ron Coderre
external usenet poster
 
Posts: 1,922
Default fill a series keeping the last number constant

If it's just for appearance...

Perhaps this:
Starter cell: 2566

From the Excel Main Menu:
formatcellsnumber 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






  #10  
Old April 24th, 2009, 07:35 PM posted to microsoft.public.excel.misc
JMart
external usenet poster
 
Posts: 1
Default 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:
formatcellsnumber 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







 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 02:36 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.