auto number columns in excel

Is there a way to set up a column of numbers so that it automatically updates 
the numbers every time a line is added or deleted?
0
Laura (120)
3/29/2005 5:43:01 AM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
655 Views

Similar Articles

[PageSpeed] 49

>-----Original Message-----
>Is there a way to set up a column of numbers so that it 
automatically updates 
>the numbers every time a line is added or deleted?
>.
>Use     =count(A1:A10)    or more rows as required. 
If its the last row used - =row(B1) extended down to 
maybe B100. Then you can use   =large(B1:B100) this will 
give you the last row used.
Mal
0
anonymous (74722)
3/29/2005 6:20:16 AM
I either did not understand the reply given or was not clear in what I am 
trying to accomplish.  I have a spreadsheet that I am trying to create a 
brand new column of numbers that are not related to the automated row numbers 
that are part of the excel program.  To clarify, my column starts on row 
three of the excel sheet, but I need it to start with number one and then 
continue on in series.  I also need it to automatically update the new row 
numbers as rows are added and deleted.  Is there a way to do this?  Please 
help!

"anonymous@discussions.microsoft.com" wrote:

> 
> >-----Original Message-----
> >Is there a way to set up a column of numbers so that it 
> automatically updates 
> >the numbers every time a line is added or deleted?
> >.
> >Use     =count(A1:A10)    or more rows as required. 
> If its the last row used - =row(B1) extended down to 
> maybe B100. Then you can use   =large(B1:B100) this will 
> give you the last row used.
> Mal
> 
0
Laura (120)
3/29/2005 7:53:02 AM
The difficulty may be in what you mean by a row is added, as a spreadsheet
consists of a fixed number of rows, albeit not all necessarily used.

To get the numbers is easy enough, =ROW(A1), and copy down, but to create
when you 'add a row' needs clarification.

-- 

HTH

RP
(remove nothere from the email address if mailing direct)


"Laura" <Laura@discussions.microsoft.com> wrote in message
news:9D0D0A30-EB25-433F-B6D1-9DD951F8CB3E@microsoft.com...
> I either did not understand the reply given or was not clear in what I am
> trying to accomplish.  I have a spreadsheet that I am trying to create a
> brand new column of numbers that are not related to the automated row
numbers
> that are part of the excel program.  To clarify, my column starts on row
> three of the excel sheet, but I need it to start with number one and then
> continue on in series.  I also need it to automatically update the new row
> numbers as rows are added and deleted.  Is there a way to do this?  Please
> help!
>
> "anonymous@discussions.microsoft.com" wrote:
>
> >
> > >-----Original Message-----
> > >Is there a way to set up a column of numbers so that it
> > automatically updates
> > >the numbers every time a line is added or deleted?
> > >.
> > >Use     =count(A1:A10)    or more rows as required.
> > If its the last row used - =row(B1) extended down to
> > maybe B100. Then you can use   =large(B1:B100) this will
> > give you the last row used.
> > Mal
> >


0
bob.phillips1 (6510)
3/29/2005 9:02:20 AM
Laura - 

This will deal with DELETED rows, but not INSERTED rows.  For inserted rows 
you'll need to copy the formula into the appropriate cell in the new row.

In the cell where you want the numbers to start enter the number 1 as a 
constant.  In the next cell down use the formula 

=OFFSET(current cell's address,-1,0)+1 (or whatever increment you want to use)

Copy it down as far as needed.  

Duke


"Laura" wrote:

> I either did not understand the reply given or was not clear in what I am 
> trying to accomplish.  I have a spreadsheet that I am trying to create a 
> brand new column of numbers that are not related to the automated row numbers 
> that are part of the excel program.  To clarify, my column starts on row 
> three of the excel sheet, but I need it to start with number one and then 
> continue on in series.  I also need it to automatically update the new row 
> numbers as rows are added and deleted.  Is there a way to do this?  Please 
> help!
> 
> "anonymous@discussions.microsoft.com" wrote:
> 
> > 
> > >-----Original Message-----
> > >Is there a way to set up a column of numbers so that it 
> > automatically updates 
> > >the numbers every time a line is added or deleted?
> > >.
> > >Use     =count(A1:A10)    or more rows as required. 
> > If its the last row used - =row(B1) extended down to 
> > maybe B100. Then you can use   =large(B1:B100) this will 
> > give you the last row used.
> > Mal
> > 
0
DukeCarey (494)
3/29/2005 1:39:05 PM
Reply:

Similar Artilces:

How can zero be displayed in Excel as the first number
Kevin Wrote: > Hi Kevin Either format the cell as Text or enter '0???, beware the numbers cannot then be used in calculations -- Paul Sheppard ------------------------------------------------------------------------ Paul Sheppard's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24783 View this thread: http://www.excelforum.com/showthread.php?threadid=400192 ...

how to know the active column is empty ?
Current cell is Y1 and I want to know the active column is empty . Please adivice me to find the column is empty or not? With thanks pol Hi, Is this a VB question? It sounds like Dim Emty As Boolean Emty = WorksheetFunction.CountA(ActiveCell.EntireColumn) = 0 Will return TRUE or FALSE -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "pol" wrote: > Current cell is Y1 and I want to know the active column is empty . Please > ...

Using Names, ie ALPHA as Purchase Order Numbers
I have been told that if i use any alpha based purchase order numbers may cause RMS to crash. In order to track customer orders it would be easy to use their name as the order number. Is this advisable? -- dave dave wrote: > I have been told that if i use any alpha based purchase order numbers may > cause RMS to crash. In order to track customer orders it would be easy to use > their name as the order number. Is this advisable? I think you can use the alpha on PO number only in prefix.. the manager found the next po number using the right chars of PO number. cod Dave I was ...

Finding a string in Excel cells
Not sure if this is an Excel problem or a general Microsoft products "feature", but here goes:- I've inherited a HUGE spreadsheet with 6 figures of cells in total(!) and I'm told that some have been highlighted for query or error with "???" in the cell (in addition to other text in that cell). Trouble is, as soon as I do "find" with the "?" character, it seems to ignore it, as if ? is some reserved character. Does anyone have any idea how I can get round this? Ta! The ? character is used as a wild card character (any single character). ...

cut and paste from explorer window into excel
I need help! How can I cut and paste several files (hundreds of them)from an Explorer window into an Excel spreadsheet without having to do them one at a time. Thanks, ...

Excel 2007 chart gridline color using vba
Hello, i want to apply a themecolor to a chart gridline using vba. I can apply normal colors (RGB, index) but not the theme color. Any idea? thx. berg Hi, Here are the various ways fo changing the colour. With ActiveChart With .Axes(xlValue, xlPrimary) If .HasMajorGridlines Then With .MajorGridlines .Border.ColorIndex = 5 'set color to blue .Border.Color = RGB(0, 255, 0) ' green .Format.Line.ForeColor.ObjectThemeColor = msoThemeColorAccent2 End With En...

Excel formula help needed!
Hello everyone, I would like to create a formula that would calculate a different levy charge dependent on the amount. The example is below and any help would be really appreciated. In circumstances where amounts owed do not exceed £100 or $100 12.5% charge will apply, 4% on the next £400 or $400, 2.5% on the next £1500 or $1500, 1% on the next £8000 or $8000 and 0.25% on any additional sum. In short different %'s will apply at different levels depending on monies mowed. I'm sure that there will be use of If/And but I'm not sure on the st...

Countif multiple criteria within the same column
I need to count different model numbers within the same column. The model numbers might be BLR, GHTR, HYRS,GUIT and so on. I need excel to count the different model numbers in this column but only if another column = "N" Any suggestions? =sumproduct(--(a1:a10="BLR"),--(b1:b10="N")) change the range, but don't use the whole column. Harley wrote: > > I need to count different model numbers within the same column. The model > numbers might be BLR, GHTR, HYRS,GUIT and so on. I need excel to count the > different model numbers in this col...

Insert / remove a row from protected sheet excel 2003 #2
Hi, I have my worksheet protected. I set the frist 5 rows all cells to Locked. The rest of the cells are Unlocked. When applying protection I ticked the boxes saying allow insert of row and allow delete rows. Yet when the protection is applied the icons / menu options for inserting / deleting rows are grayed out. What am I doing wrong? Thanks! Michiel. ...

identifying odd/even numbers
Is there a formula or easy way way for excel to identify if a number (thousands of numbers) is odd or even? Thanks --- Message posted from http://www.ExcelForum.com/ One way: Even: =MOD(A1,2)=0 Odd: =MOD(A1,2)=1 If you load the Analysis Toolpak Add-in, you can use the ISODD() or ISEVEN() functions. Note that ISODD works a bit differently, since it truncates numbers, so if A1 = 3.5 =MOD(A1,2)=1 ==> FALSE =ISODD(A1) ==> TRUE In article <rdy4trvl.z2p1m@excelforum-nospam.com>, rdy4trvl <rdy4trvl.z2p1m@excelforum-nospam.com> wrote: > Is ...

filling in a column
I have exported data which gives me details of invoices by client, however the client column is populated just once, while there can be more than one invoice per client. How can I create a macro that fills in the client column automatically up to the next client name and then continues to full the column with this next client until it finds no other client. I have tried to explain the issue below Column 1 Column 2 Client 1 Invoice 1 Invoice 2 Invoice 3 Client2 Invoice 4 Invoice 5 Clientn Invoice x How can I populate the Column 1 with the client nam...

Excel asking for save with no changes #3
Yes, "=NOW()". I'll try removing it. Thanks -- tommy2 ----------------------------------------------------------------------- tommy20's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=281 View this thread: http://www.excelforum.com/showthread.php?threadid=27781 ...

A Pop up message in Excel...
Just curious if this can be done... does anyone know a way to make a pop up message appear in Excel 2003 when an a file opens? Example, I have a particular file that is shared for others to update. When whoever opens the file I would like a little text box to say a few chosen words from me like "THIS FILE IS NOT TO BE MOVE OR RENAMED" or anything else I choose. Thanks in advance, Ian Hi Ian, In a standard module paste: '=============>> Public Sub Auto_Open() MsgBox "THIS FILE IS NOT TO BE MOVE OR RENAMED" End Sub '<<============= --- ...

Number format for basis points
Hello I am certainly not the first to ask but I didn't find an answer yet. Is there anyway to create a custom number format that allows to display basis points in Excel? For instance if I have a cell with the formula "=2.5%%", I would like to see "2.5bps" appearing. "0.0%%" allows to get "2.5%%" appearing. Any trick to get beyond that step? thanks in advance Charles Use a custom number format of 0.0"bps" and just enter 2.5 -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Charl...

Place a form control value in Access to a specific cell in Excel.
I didn't get a response on my last post, so I'm hoping someone will have an idea on this request. Below is my original post: Hi, All! I'm looking for some help in moving data from my Access form to specific cells in an Excel template. I'd like to create a command button on my Access form that will open the .xlt file and transfer some of the values on my form and plug them into the .xlt. For example, I have the cost of equipment calculated on my form control (3 printers @ $300 each = $900), the command button will capture that value and plug it into the capital expenditure ...

Top row of excel stays even in sorting
I need a header in the top row of my excel worksheet so that even when I sort the data the top row remains the same. 2003:- Data>>Sort>>My Data Range As>> Check the “Header Row” Option Button. 2007:- Press Alt+D+S the Sort Dialog Box will appear Press Alt+H which will select the “My Data has headers” check box. -- Remember to Click Yes, if this post helps! -------------------- (Ms-Exl-Learner) -------------------- "willyd" wrote: > I need a header in the top row of my excel worksheet so that even when I sort > the data the top ro...

prohibit users from creating new sites/part numbers
GP 8.0 when you type in a site for example in the transfer screen and the site doesn't exist, the program asks you if you want to create it. We do NOT want anyone creating new sites or part numbers who isn't in the Logistics group. Is there any way to prohibit this behavior by the program. I 've looked all through advanced security and turned off the cards for a certain class of user, but the program still asks if they want to create a new site/part number. I do not have Field Level Security module. Thanks! Margaret Margaret, The next step, after the user says Yes to se...

Year over Year (monthly) comparisons when Monthly Values are in a single Column
Is there a best method of showing Year over Year chart comparisons (data displayed by Month) when the monthly data points are displayed in a single column? Thanks for any suggestions or examples. Data is displayed like this: 201011 Value 201010 Value 201009 Value Etc.... On Apr 22, 1:30=A0pm, Dave K <fred.sher...@gmail.com> wrote: > Is there a best method of showing Year over Year chart > comparisons (data displayed by Month) when the monthly > data points are displayed in a single column? [....] > Data is displayed like this: > 201011 =A0 Value > 201010 =A0 ...

Looking for Excel 95
I'm trying to help a friend who has windows 95, we're looking for an Excel download that would work with that OS. I haven't found anything at microsoft.com, all I can find are updates and patches. (Will he need Office 97, or was Excel included in the standard windows 95 release?). Ideally a free Excel download would be best, or a 30 day trial version. Any thoughts for the 95? Thanks. Abe I'm sorry to say this, but it would be cheaper to upgrade the OS tha to find a version of Excel that will run on windows 95. Remember time is $$ as well Excel was never included with a...

Create separate chart for each row in excel
I am attempting to create a separate chart for each row of my worksheet (about 300 rows = 300 separate charts). Is there an easy way to do this? The actual data range will change month-to-month, so I would not want to have to recreate 300 new charts each month individually. While I have never created (or tried to create) 300 charts in a single workbook, it is easy to believe that performance and stability of Excel might become a question. Do you have to have 300 charts? Or will one chart (or a few) that can be easily changed to reflect new data suffice? If so, adapt the ideas behind ...

Create duplicate from fields Plus auto generate
Help would be appreciated, even if it can’t be done I have a table[InspectorsMaster] using Fields, ([DR_Date], (Short Date). A number field (Long Integer [SeqNum])I do not currently use it, and a Field ([Employee], Text field, size set at 3). I would like to know if I could generate a Unique sequential Value (starting with 0000) and combine it with values from date, and text field that would automaticly be entered into the Non used Number field such as on click . This of course would occure at The Form level before close of the form. (Macro) or after dirty of the Date field. Li...

Excel 2000 capitalization help
We have a 1500 record spreadsheet that has mixed lower and upper case in their cells. We would like to convert all records to upper case automatically. We've tried the formula Excel provides =Upper (cell to cell) without any luck. does anyone have any suggestions? Thx DAN Hi see: http://www.cpearson.com/excel/case.htm -- Regards Frank Kabel Frankfurt, Germany Dan wrote: > We have a 1500 record spreadsheet that has mixed lower > and upper case in their cells. We would like to convert > all records to upper case automatically. We've tried the > formula Excel pro...

Excel 2007 to 2008: Headers with color text bad
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel I searched the forum, but couldn't find any answers to this... <br><br>I was getting odd text headers in Excel 2008 in some files brought from a Windows machine running Excel 2007. <br><br>The numbers looked familiar and then I recognized they were the HTML code for colors. <br><br>As best I can tell, Excel 2007 for Windows can have colored text in headers and footers, but Excel 2008 for the Mac cannot. When a file with colored text in the header is opened in Excel 2008, t...

& IN EXCEL HEADER AND FOOTER
Hi everbody, How do I put an & in an excel header and footer? It seems to disappear. Thanks! Matt. Hi try entering && -- Regards Frank Kabel Frankfurt, Germany "Matt" <anonymous@discussions.microsoft.com> schrieb im Newsbeitrag news:75cb01c4845a$0e1e3400$a501280a@phx.gbl... > Hi everbody, > > How do I put an & in an excel header and footer? It > seems to disappear. > > Thanks! > > Matt. ...

12.0.1 Does not show up in auto-updater #2
In article <ee93bac.-1@webcrossing.caR9absDaxw>, <socokid@officeformac.com> wrote: > I have seen this on each of the nearly 20 machine's I've tried this on > (different companies). When I click the "check for updates" in Word, it > always says "There are no updates available at this time". Each time I have > had to come to this site to download the 12.0.1 update and install in > manually. > > What is wrong with the updater application? It hasn't caught this update ONCE > yet on any of the machines I have tested. Again, diffe...