Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 28
Default Formula for serial number

sir,

In my range of data, the first column is of serial number where in I put 1
in A1 and use the formula A1+1 in A2 and copying the same downwards. It
works perfectly unless I delete a row the range. When I delete a row #REF!
will appear in the remaining rows. Is any way to avoid this, i mean , when I
delete a row the formula should catch the number in the above row +1 in the
next.

please help
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 11,501
Default Formula for serial number

Try:-

In A1 =ROW(A1)

Drag down and delete a row and see what happens.

Mike

"shaji" wrote:

sir,

In my range of data, the first column is of serial number where in I put 1
in A1 and use the formula A1+1 in A2 and copying the same downwards. It
works perfectly unless I delete a row the range. When I delete a row #REF!
will appear in the remaining rows. Is any way to avoid this, i mean , when I
delete a row the formula should catch the number in the above row +1 in the
next.

please help

  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 8,856
Default Formula for serial number

You could just use:

=ROW()

to generate your sequence. This would automatically adjust if you
delete a row.

Hope this helps,

Pete


On Jun 25, 10:41 am, shaji wrote:
sir,

In my range of data, the first column is of serial number where in I put 1
in A1 and use the formula A1+1 in A2 and copying the same downwards. It
works perfectly unless I delete a row the range. When I delete a row #REF!
will appear in the remaining rows. Is any way to avoid this, i mean , when I
delete a row the formula should catch the number in the above row +1 in the
next.

please help



  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 28
Default Formula for serial number

It works only when the serial number is same as of the row number.

is there any other way



"Pete_UK" wrote:

You could just use:

=ROW()

to generate your sequence. This would automatically adjust if you
delete a row.

Hope this helps,

Pete


On Jun 25, 10:41 am, shaji wrote:
sir,

In my range of data, the first column is of serial number where in I put 1
in A1 and use the formula A1+1 in A2 and copying the same downwards. It
works perfectly unless I delete a row the range. When I delete a row #REF!
will appear in the remaining rows. Is any way to avoid this, i mean , when I
delete a row the formula should catch the number in the above row +1 in the
next.

please help




  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5,651
Default Formula for serial number

On Mon, 25 Jun 2007 02:41:03 -0700, shaji
wrote:

sir,

In my range of data, the first column is of serial number where in I put 1
in A1 and use the formula A1+1 in A2 and copying the same downwards. It
works perfectly unless I delete a row the range. When I delete a row #REF!
will appear in the remaining rows. Is any way to avoid this, i mean , when I
delete a row the formula should catch the number in the above row +1 in the
next.

please help


Change your formula

A2: =$A$1+ROWS($1:1)



--ron


  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 28
Default Formula for serial number

Thanks, it is working perfectly.

"Ron Rosenfeld" wrote:

On Mon, 25 Jun 2007 02:41:03 -0700, shaji
wrote:

sir,

In my range of data, the first column is of serial number where in I put 1
in A1 and use the formula A1+1 in A2 and copying the same downwards. It
works perfectly unless I delete a row the range. When I delete a row #REF!
will appear in the remaining rows. Is any way to avoid this, i mean , when I
delete a row the formula should catch the number in the above row +1 in the
next.

please help


Change your formula

A2: =$A$1+ROWS($1:1)



--ron

  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5,651
Default Formula for serial number

On Mon, 25 Jun 2007 04:51:00 -0700, shaji
wrote:

Thanks, it is working perfectly.

You're welcome. Glad to help.
--ron
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
serial number decode chiuinggum Excel Worksheet Functions 6 June 13th 06 09:15 AM
Serial Number List Rowf Excel Discussion (Misc queries) 3 September 21st 05 05:55 PM
serial number cell LGCoop Excel Worksheet Functions 2 September 10th 05 09:14 PM
Serial number allocation - Array formula Space Ape Excel Worksheet Functions 0 July 3rd 05 07:32 AM
generate serial number john douglass Excel Worksheet Functions 0 November 5th 04 11:49 PM


All times are GMT +1. The time now is 12:27 PM.

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

About Us

"It's about Microsoft Excel"