| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2002: How to assign index numbers in block ?
Dear Sir,
I have the following worksheet : A B C D 1 1 CX1245 3/22/2007 250 2 1 CX1246 4/27/2007 100 3 1 TOTAL 350 4 2 DX2241 6/21/2003 300 5 2 DX2242 4/7/2003 100 6 2 DX2246 8/12/2004 50 7 2 TOTAL 450 8 3 DX2632 6/21/2003 300 9 3 DX2644 6/24/2003 100 10 3 DX2322 4/17/2003 100 11 3 DX4500 8/12/2004 50 12 3 TOTAL 550 13 4 XV4177 8/9/2001 210 14 4 XV4178 9/8/2002 100 15 4 TOTAL 550 May I know what formula I must input at cell A1 and copy down to get the index numbers as illustrated ? How about if I want to start the number with 50, how the formula will look like then ? Thanks Low -- A36B58K641 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel 2002: How to assign index numbers in block ?
hi'
Assuming that your data starts at A1...enter the first number of your sequence...say 50 per your example. then in A2 enter.... =sum(a1+1). copy down. regards FSt1 "Mr. Low" wrote: Dear Sir, I have the following worksheet : A B C D 1 1 CX1245 3/22/2007 250 2 1 CX1246 4/27/2007 100 3 1 TOTAL 350 4 2 DX2241 6/21/2003 300 5 2 DX2242 4/7/2003 100 6 2 DX2246 8/12/2004 50 7 2 TOTAL 450 8 3 DX2632 6/21/2003 300 9 3 DX2644 6/24/2003 100 10 3 DX2322 4/17/2003 100 11 3 DX4500 8/12/2004 50 12 3 TOTAL 550 13 4 XV4177 8/9/2001 210 14 4 XV4178 9/8/2002 100 15 4 TOTAL 550 May I know what formula I must input at cell A1 and copy down to get the index numbers as illustrated ? How about if I want to start the number with 50, how the formula will look like then ? Thanks Low -- A36B58K641 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel 2002: How to assign index numbers in block ?
One way ..
Put in A1: =IF(C1="TOTAL",COUNTIF($C$1:C1,"TOTAL"),COUNTIF($C $1:C1,"TOTAL")+1) Copy down And if you want it to start from 50. adjust it like this in A1: =IF(C1="TOTAL",COUNTIF($C$1:C1,"TOTAL")+49,COUNTIF ($C$1:C1,"TOTAL")+50) Copy down -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Mr. Low" wrote: Dear Sir, I have the following worksheet : A B C D 1 1 CX1245 3/22/2007 250 2 1 CX1246 4/27/2007 100 3 1 TOTAL 350 4 2 DX2241 6/21/2003 300 5 2 DX2242 4/7/2003 100 6 2 DX2246 8/12/2004 50 7 2 TOTAL 450 8 3 DX2632 6/21/2003 300 9 3 DX2644 6/24/2003 100 10 3 DX2322 4/17/2003 100 11 3 DX4500 8/12/2004 50 12 3 TOTAL 550 13 4 XV4177 8/9/2001 210 14 4 XV4178 9/8/2002 100 15 4 TOTAL 550 May I know what formula I must input at cell A1 and copy down to get the index numbers as illustrated ? How about if I want to start the number with 50, how the formula will look like then ? Thanks Low -- A36B58K641 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel 2002: How to assign index numbers in block ?
In A1: enter 1
In A2: =IF(C1="TOTAL",A1+1,A1) copy down "Mr. Low" wrote: Dear Sir, I have the following worksheet : A B C D 1 1 CX1245 3/22/2007 250 2 1 CX1246 4/27/2007 100 3 1 TOTAL 350 4 2 DX2241 6/21/2003 300 5 2 DX2242 4/7/2003 100 6 2 DX2246 8/12/2004 50 7 2 TOTAL 450 8 3 DX2632 6/21/2003 300 9 3 DX2644 6/24/2003 100 10 3 DX2322 4/17/2003 100 11 3 DX4500 8/12/2004 50 12 3 TOTAL 550 13 4 XV4177 8/9/2001 210 14 4 XV4178 9/8/2002 100 15 4 TOTAL 550 May I know what formula I must input at cell A1 and copy down to get the index numbers as illustrated ? How about if I want to start the number with 50, how the formula will look like then ? Thanks Low -- A36B58K641 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel 2002: How to assign index numbers in block ?
Hello Max,
Many thanks for your formula. It works perfectly well. Kind Regards Low -- A36B58K641 "Max" wrote: One way .. Put in A1: =IF(C1="TOTAL",COUNTIF($C$1:C1,"TOTAL"),COUNTIF($C $1:C1,"TOTAL")+1) Copy down And if you want it to start from 50. adjust it like this in A1: =IF(C1="TOTAL",COUNTIF($C$1:C1,"TOTAL")+49,COUNTIF ($C$1:C1,"TOTAL")+50) Copy down -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Mr. Low" wrote: Dear Sir, I have the following worksheet : A B C D 1 1 CX1245 3/22/2007 250 2 1 CX1246 4/27/2007 100 3 1 TOTAL 350 4 2 DX2241 6/21/2003 300 5 2 DX2242 4/7/2003 100 6 2 DX2246 8/12/2004 50 7 2 TOTAL 450 8 3 DX2632 6/21/2003 300 9 3 DX2644 6/24/2003 100 10 3 DX2322 4/17/2003 100 11 3 DX4500 8/12/2004 50 12 3 TOTAL 550 13 4 XV4177 8/9/2001 210 14 4 XV4178 9/8/2002 100 15 4 TOTAL 550 May I know what formula I must input at cell A1 and copy down to get the index numbers as illustrated ? How about if I want to start the number with 50, how the formula will look like then ? Thanks Low -- A36B58K641 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel 2002: How to assign index numbers in block ?
Hello Madam,
Many thanks for your formula. It works perfectly well. Kind Regards Low -- A36B58K641 "Teethless mama" wrote: In A1: enter 1 In A2: =IF(C1="TOTAL",A1+1,A1) copy down "Mr. Low" wrote: Dear Sir, I have the following worksheet : A B C D 1 1 CX1245 3/22/2007 250 2 1 CX1246 4/27/2007 100 3 1 TOTAL 350 4 2 DX2241 6/21/2003 300 5 2 DX2242 4/7/2003 100 6 2 DX2246 8/12/2004 50 7 2 TOTAL 450 8 3 DX2632 6/21/2003 300 9 3 DX2644 6/24/2003 100 10 3 DX2322 4/17/2003 100 11 3 DX4500 8/12/2004 50 12 3 TOTAL 550 13 4 XV4177 8/9/2001 210 14 4 XV4178 9/8/2002 100 15 4 TOTAL 550 May I know what formula I must input at cell A1 and copy down to get the index numbers as illustrated ? How about if I want to start the number with 50, how the formula will look like then ? Thanks Low -- A36B58K641 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel 2002: How to assign index numbers in block ?
Dear Sir,
I tried your formula today, but I could not get what I wanted as illustrated. The number are running in sequence for each row instead of running in blocks. ie 1,1,1,2,2,2,3,3 and so on I suppose the following formula could do the job. In A1: enter 1 In A2: =IF(C1="TOTAL",A1+1,A1) copy down Thank you anyway for your prompt response. Low -- A36B58K641 "FSt1" wrote: hi' Assuming that your data starts at A1...enter the first number of your sequence...say 50 per your example. then in A2 enter.... =sum(a1+1). copy down. regards FSt1 "Mr. Low" wrote: Dear Sir, I have the following worksheet : A B C D 1 1 CX1245 3/22/2007 250 2 1 CX1246 4/27/2007 100 3 1 TOTAL 350 4 2 DX2241 6/21/2003 300 5 2 DX2242 4/7/2003 100 6 2 DX2246 8/12/2004 50 7 2 TOTAL 450 8 3 DX2632 6/21/2003 300 9 3 DX2644 6/24/2003 100 10 3 DX2322 4/17/2003 100 11 3 DX4500 8/12/2004 50 12 3 TOTAL 550 13 4 XV4177 8/9/2001 210 14 4 XV4178 9/8/2002 100 15 4 TOTAL 550 May I know what formula I must input at cell A1 and copy down to get the index numbers as illustrated ? How about if I want to start the number with 50, how the formula will look like then ? Thanks Low -- A36B58K641 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel 2002: How to assign index numbers in block ?
Welcome, Low.
Think the earlier suggestion was overdone. Go with the better one by TM. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Mr. Low" wrote in message ... Hello Max, Many thanks for your formula. It works perfectly well. Kind Regards Low |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excel 2002 : How to get the difference in a block of data ? | Excel Discussion (Misc queries) | |||
| HOW DO I BLOCK DUPLICATE NUMBERS IN EXCEL | Excel Worksheet Functions | |||
| setting a tab index within excel 2002 | Excel Discussion (Misc queries) | |||
| how to auto assign po numbers in Excel | Excel Worksheet Functions | |||
| how to auto assign po numbers in Excel | Excel Worksheet Functions | |||
Linear Mode
