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. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|