Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 19
Default Auto-update row numbers after inserting new row

I have a spreadsheet with Column A numbered sequentially using auto fill
series. If someone adds a new row between 2 existing numbered rows, is there
any way to automatically update the row numbering?

Thanks!
--
carusso
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,856
Default Auto-update row numbers after inserting new row

If you put this formula in A1:

=ROW()

and copy it down as far as you need, then it will always show the
correct row. If you insert a new row, however, then the new row will
not have the formula in it.

Hope this helps.

Pete

On Jan 23, 11:46*pm, carusso
wrote:
I have a spreadsheet with Column A numbered sequentially using auto fill
series. *If someone adds a new row between 2 existing numbered rows, is there
any way to automatically update the row numbering?

Thanks!
--
carusso


  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 19
Default Auto-update row numbers after inserting new row

Thanks for your response, Pete. I have tried the Row() feature also. But I
need some way [if it exists! ;-)] to have the row numbers automatically
update after the new row is added. The spreadsheet is being used by people
that really don't have a clue what to do with Excel, and the SAS program that
will be reading the spreadsheet requires the correct row number be in the
spreadsheet.

Cheryl
--
carusso


"carusso" wrote:

I have a spreadsheet with Column A numbered sequentially using auto fill
series. If someone adds a new row between 2 existing numbered rows, is there
any way to automatically update the row numbering?

Thanks!
--
carusso

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,856
Default Auto-update row numbers after inserting new row

Well, to do that you would need a macro, and if you do a Google search
I'm sure you will find many suggested.

But, if you go that route and if your users are not very competant
with Excel then how could you ensure that they would enable macros to
run when they start Excel?

Pete

On Jan 26, 4:03*pm, carusso wrote:
Thanks for your response, Pete. *I have tried the Row() feature also. *But I
need some way [if it exists! ;-)] to have the row numbers automatically
update after the new row is added. *The spreadsheet is being used by people
that really don't have a clue what to do with Excel, and the SAS program that
will be reading the spreadsheet requires the correct row number be in the
spreadsheet.

Cheryl
--
carusso



"carusso" wrote:
I have a spreadsheet with Column A numbered sequentially using auto fill
series. *If someone adds a new row between 2 existing numbered rows, is there
any way to automatically update the row numbering?


Thanks!
--
carusso- Hide quoted text -


- Show quoted text -


  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 19
Default Auto-update row numbers after inserting new row

Well, unfortunately, I can't guarantee they would enable macros. If they see
the dialog box to enable/disable macros (which as a default...most probably
still have this feature turned on), it probably scares them and they click
"disable"...lol
--
carusso


"Pete_UK" wrote:

Well, to do that you would need a macro, and if you do a Google search
I'm sure you will find many suggested.

But, if you go that route and if your users are not very competant
with Excel then how could you ensure that they would enable macros to
run when they start Excel?

Pete

On Jan 26, 4:03 pm, carusso wrote:
Thanks for your response, Pete. I have tried the Row() feature also. But I
need some way [if it exists! ;-)] to have the row numbers automatically
update after the new row is added. The spreadsheet is being used by people
that really don't have a clue what to do with Excel, and the SAS program that
will be reading the spreadsheet requires the correct row number be in the
spreadsheet.

Cheryl
--
carusso



"carusso" wrote:
I have a spreadsheet with Column A numbered sequentially using auto fill
series. If someone adds a new row between 2 existing numbered rows, is there
any way to automatically update the row numbering?


Thanks!
--
carusso- Hide quoted text -


- Show quoted text -





  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 22,906
Default Auto-update row numbers after inserting new row

Double-click event code to insert a row and have the number update when
using the =ROW() function in column A

Private Sub Worksheet_BeforeDoubleClick(ByVal Target _
As Range, Cancel As Boolean)
If Target.Column = 1 And Target.Row < 1 Then
ActiveCell.EntireRow.Insert
ActiveCell.Value = ActiveCell.Offset(-1, 0).Formula
End If
Cancel = True
End Sub


Have a contingency plan that will render the workbook unusable if they
disable macros.

Here is a sample................

Create a sheet named Dummy with a large message typed in the middle.

"You have disabled Macros and this workbook is useless without them. Please
close and re-open with macros enabled"

Then add these two event codes to Thisworkbook module.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim sht As Worksheet
Application.ScreenUpdating = False
Sheets("Dummy").Visible = xlSheetVisible
For Each sht In ActiveWorkbook.Sheets
If sht.Name < "Dummy" Then
sht.Visible = xlSheetVeryHidden
End If
Next sht
Application.ScreenUpdating = True
ThisWorkbook.Save
End Sub

Private Sub Workbook_Open()
Dim sht As Worksheet
Application.ScreenUpdating = False
For Each sht In ActiveWorkbook.Sheets
If sht.Name < "Dummy" Then
sht.Visible = True
Sheets("Dummy").Visible = xlSheetVeryHidden
End If
Next sht
Application.ScreenUpdating = True
End Sub


Gord Dibben MS Excel MVP

On Mon, 26 Jan 2009 08:47:03 -0800, carusso
wrote:

Well, unfortunately, I can't guarantee they would enable macros. If they see
the dialog box to enable/disable macros (which as a default...most probably
still have this feature turned on), it probably scares them and they click
"disable"...lol


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
auto update formula when inserting additional columns dlisman Excel Worksheet Functions 1 August 9th 07 04:28 PM
Inserting a row (update) Tom Excel Discussion (Misc queries) 2 February 15th 06 06:48 PM
Auto Inserting numbers Buildscharacter Excel Worksheet Functions 1 November 14th 05 04:34 AM
Auto Update upon shifting, inserting or deleting Tracy B Excel Worksheet Functions 2 April 6th 05 08:37 PM
excel links update not working in auto, calculations in auto Mikey Boy Excel Worksheet Functions 0 December 7th 04 11:53 PM


All times are GMT +1. The time now is 12:39 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"