Sequential Cell Numbering

Version: 2008
Operating System: Mac OS X 10.6 (Snow Leopard)
Processor: Intel

I'm trying to create an Excel spreadsheet with one column indicating sequential line numbers; 1, 2, 3, etc..  My goal is to have a formula where, for example, the 6th line down displays 6 by adding 1 to the line 5 above it.  The reason is if there are 50 lines I don't have to number each one manually but the problem I'm encountering is if one line is deleted then all subsequent lines show "#REF!" error message.  The formula I created is "=IF(A5>0, A5+1, A4+1)", with the idea that if line 5 were deleted, the formula would look to the next line up (4) and resume the sequence; by deleting the line the result is "=IF(#REF!>0,#REF!+ 1, A6+1)".  The problem as best I can determine is by deleting line 5 it doesn't update all formulas below line 5.  Any suggestions, or other formulas I should use.  Thanks
0
EW64
6/3/2010 10:26:16 PM
mac.office.excel 1146 articles. 0 followers. Follow

3 Replies
1457 Views

Similar Articles

[PageSpeed] 16

This is such a pain :-)  It's easy in VBA, but you don't have any of that...

You have to test for the error.

The following works:

=IF(ISERR(A5),A4+1,A5+1)

Of course, you can delete only ONE row at a time.  But you could extend the
concept with nesting...

Essentially, you have to test "up" the column until you find a good one and
use that.

Cheers

On 4/06/10 8:26 AM, in article 59bb9562.-1@webcrossing.JaKIaxP2ac0,
"EW64@officeformac.com" <EW64@officeformac.com> wrote:

> Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel
> I'm trying to create an Excel spreadsheet with one column indicating
> sequential line numbers; 1, 2, 3, etc..  My goal is to have a formula where,
> for example, the 6th line down displays 6 by adding 1 to the line 5 above it.
> The reason is if there are 50 lines I don't have to number each one manually
> but the problem I'm encountering is if one line is deleted then all subsequent
> lines show "#REF!" error message.  The formula I created is "=IF(A5>0, A5+1,
> A4+1)", with the idea that if line 5 were deleted, the formula would look to
> the next line up (4) and resume the sequence; by deleting the line the result
> is "=IF(#REF!>0,#REF!+ 1, A6+1)".  The problem as best I can determine is by
> deleting line 5 it doesn't update all formulas below line 5.  Any suggestions,
> or other formulas I should use.  Thanks

 --

The email below is my business email -- Please do not email me about forum
matters unless I ask you to; or unless you intend to pay!

John McGhie, Microsoft MVP (Word, Mac Word), Consultant Technical Writer,
McGhie Information Engineering Pty Ltd
Sydney, Australia. | Ph: +61 (0)4 1209 1410 | mailto:john@mcghie.name


0
John_McGhie_
6/5/2010 5:37:10 AM
On 2010-06-05 01:37:10 -0400, John_McGhie_[MVP]@officeformac.com said:

> This is such a pain :-)  It's easy in VBA, but you don't have any of that...
> 
> You have to test for the error.
> 
> The following works:
> 
> =IF(ISERR(A5),A4+1,A5+1)
> 
> Of course, you can delete only ONE row at a time.  But you could extend the
> concept with nesting...
> 
> Essentially, you have to test "up" the column until you find a good one and
> use that.
> 
> Cheers
> 
> On 4/06/10 8:26 AM, in article 59bb9562.-1@webcrossing.JaKIaxP2ac0,
> "EW64@officeformac.com" <EW64@officeformac.com> wrote:
> 
>> Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel
>> I'm trying to create an Excel spreadsheet with one column indicating
>> sequential line numbers; 1, 2, 3, etc..  My goal is to have a formula where,
>> for example, the 6th line down displays 6 by adding 1 to the line 5 above it.
>> The reason is if there are 50 lines I don't have to number each one manually
>> but the problem I'm encountering is if one line is deleted then all subsequent
>> lines show "#REF!" error message.  The formula I created is "=IF(A5>0, A5+1,
>> A4+1)", with the idea that if line 5 were deleted, the formula would look to
>> the next line up (4) and resume the sequence; by deleting the line the result
>> is "=IF(#REF!>0,#REF!+ 1, A6+1)".  The problem as best I can determine is by
>> deleting line 5 it doesn't update all formulas below line 5.  Any suggestions,
>> or other formulas I should use.  Thanks
> 
>  --
> 
> The email below is my business email -- Please do not email me about forum
> matters unless I ask you to; or unless you intend to pay!
> 
> John McGhie, Microsoft MVP (Word, Mac Word), Consultant Technical Writer,
> McGhie Information Engineering Pty Ltd
> Sydney, Australia. | Ph: +61 (0)4 1209 1410 | mailto:john@mcghie.name

Pardon me for jumping in her late, but instead of the formula you are 
using, try =row().  That will give you the row number wich will stay 
where you want it wne you delete lines.
-- 
bobg@nospam.com

0
Bob
6/5/2010 7:26:21 PM
On 6/06/10 5:26 AM, in article 2010060515262163124-bobg@nospamcom, "Bob
Greenblatt [MVP]" <bobg@nospam.com> wrote:

> Pardon me for jumping in her late,

Unforgiveable!!  Not the "jumping in" bit, the "late" bit.

You any idea how long I tried to get that to work?  It never occurred to me
that ROW would work without an argument :-)

>  but instead of the formula you are
> using, try =row().  That will give you the row number wich will stay
> where you want it wne you delete lines.

So =ROW()-4 if you do not want the cell number to be the actual row number
in the spreadsheet ...

Many thanks :-)

 --

The email below is my business email -- Please do not email me about forum
matters unless I ask you to; or unless you intend to pay!

John McGhie, Microsoft MVP (Word, Mac Word), Consultant Technical Writer,
McGhie Information Engineering Pty Ltd
Sydney, Australia. | Ph: +61 (0)4 1209 1410 | mailto:john@mcghie.name


0
John_McGhie_
6/6/2010 8:37:36 AM
Reply:

Similar Artilces:

Serial number error in sales order entry window (2nd post)
I'm reposting this because there was no response to my first post. I'm sure I'm not the only one that has had this problem. I noticed that one of my coworkers invoiced and added (overided) a serial number into the Great Plains instead of allocating one of the existing ones. How do I return this serial number w/out affecting my inventory? I need to: 1. Return the item w/the wrong serial number. 2. Reinvoice the correct serial number. 3. NOT HAVE THE WRONG SERIAL NUMBER STUCK IN MY INVENTORY. How do I do this? Hi Paoakalani Try looking at Serial Number edit, by Blue Moon...

how do I create a macro to select all colored cells in a worksheet
Need help writing the code for an Excel Macro that would find all cells that have any interior color so that an outer border can be added around all colored cells. Hi, Try this. I've included the code to add the borders but you can delete this if you want to do it manually. Not this will not work for conditionally formatted coloured cells Sub sonic() Dim CopyRange As Range For Each c In ActiveSheet.UsedRange If c.Interior.ColorIndex <> xlNone Then If CopyRange Is Nothing Then Set CopyRange = c Else Set CopyRange = Union(C...

change the format of cell
I have uploaded an excell spreadsheet and seem that all cells are text formatted. I can`t change to fromat of cell to number. "Format cell" dosen`t working or change anything. On Tue, 27 Oct 2009 07:13:01 -0700, Exceller <Exceller@discussions.microsoft.com> wrote: >I have uploaded an excell spreadsheet and seem that all cells are text >formatted. >I can`t change to fromat of cell to number. >"Format cell" dosen`t working or change anything. Changing the format will not do that. You need to coerce these values to numbers by performing a math operation...

linking cells #3
I have a calendar created in Excel - each sheet is a new month that contains the following information: the last week of the previous month, the current month, and the first week of the next month. I would like to link the cells from the "overlapping" weeks, but I do not want the "0" to show up in the cells. Is there any way to prevent this? Thanks in advance! Paula Either choose not to show zero values through Tools>Options>View or trap the zero and turn to a blank-looking character. =IF(sheet1!A1="","",sheet1!A1) Gord Dibben Excel M...

if cell A is +- 10% of cell B
Hi What is the best way to write if statement to show if cell A1 is 10% greater or 10%smaller than cell B1 thanks Pat Hi Tina =IF(OR(A1>B1*(1+10%),A1<B1*(1-10%)),"Yes","No") Note this excludes equal to +10% and -10% values themselves. Change > to >= to include these values. Regards Roger Govier tina wrote: >Hi >What is the best way to write if statement to show if cell A1 is 10% greater >or 10%smaller than cell B1 >thanks >Pat > > Roger, I believe the phrasing in Tina's request contains an OR, but what she really means i...

tranposing every third cell in a row to a column
How do you transpose every third cell in a row of data to a column on a separate worksheet. There should be no empty cells in the column of the separate worksheet that this information is being copied to. Say the data was in Row 1. This will go from A to D to G ... etc. Enter this formula anywhere, and copy down: =INDEX(Sheet1!$1:$1,3*ROWS($1:1)-2) -- HTH, RD ===================================================== Please keep all correspondence within the Group, so all may benefit! ===================================================== "Wabash" <Wabash@discussions.micro...

How do you move the curser around in a cell
What do mean by "cursor" and what do you want to do? HTH Otto "krisf" <krisf@discussions.microsoft.com> wrote in message news:8BD5F197-4745-4FD3-95BE-80A10897020F@microsoft.com... > ...

Reference a referenced cell
Sheet1!C4 contains the formula =Sheet2!A1 I want Sheet1!D4 to find out what cell Sheet1!C4 is referencing and then return the value two columns over. (Offset will take care of returning the value two columns over). My problem is I need to know which cell Sheet1!C$ is referencing. If I use the formula "=OFFSET(Sheet1!C4,0,1)" I get the value from one column to the right of Sheet1!C4, I need the value of one column to the right of the cell being referenced by Sheet1!C4, (Sheet2!A1) How would I do this.? Hi if you have unique values in column A of your first sheet try =VLOOKUP...

Controlling Sequential Numbers
I have been researching using sequential numbers for invoices an purchase orders, but all of them seem to advance the number eithe every time the document is opened or saved. My purchase order is par of a larger file, and since I will be opening and saving the fil without necessarily writing a purchase order I need more control ove its advancement. I was wondering how I could create a button that whe pressed or selected would advance the number by 1? Also, how could make it create a unique number depending on who's creating the purchas order. For example, let's say the user is ...

Joining formatted cells #2
Magnificent!! Thanks for the speedy replies, that worked to perfection! Take th rest of the day off...tell 'em I said it was OK -- tonywa ----------------------------------------------------------------------- tonywar's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1550 View this thread: http://www.excelforum.com/showthread.php?threadid=27081 ...

Numbering Headers/
Hi Silver, Try going to File/Page Setup/Header-Footer/Custom Header/Should be the button shown in the header box with the # sign in it. Larry -- keithl816 ------------------------------------------------------------------------ keithl816's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=21287 View this thread: http://www.excelforum.com/showthread.php?threadid=498476 ...

Sequential Numbering
I am trying to automatically print sequential numbers on individual cards on 8.5" x 11" business card stock (to create tickets) using MS Publisher and cannot figure out how to get it to do this. Does anyone know the secret? Thanks. Hi Scott (scottmarsh@excite.com), in the newsgroups you posted: || I am trying to automatically print sequential numbers on individual || cards on 8.5" x 11" business card stock (to create tickets) using MS || Publisher and cannot figure out how to get it to do this. Does || anyone know the secret? Thanks. Check out the following tutorial:...

Visible Cell Fill Color
In Excel (as well as other programs) the cell fill color does not seem to be visible from the user screen. In print preview I can see it. There are similar problems in Word and PowerPoint, but Excel is what I miss most. Any help is very appreciated. Thank you, Paul Try this:- MS Article found at http://support.microsoft.com/default.aspx?scid=kb;en-ca;Q320531 OFF: Changes to Fill Color and Fill Pattern Are Not Displayed The information in this article applies to: Microsoft Excel 2000 Microsoft Excel 2002 Microsoft PowerPoint 2002 Microsoft Publisher 2002 Microsoft Word 2002 Microsoft...

numbering rows, but skipping blanks
I'm using Column A of a spreadsheet to number some items in Column B. I've been using Excel's automatic series-filling function, where you drag down from the lower right corner of a cell. However, since some of the rows in Column B are empty, and I don't want them numbered, I have to stop numbering and manually "jump" over the blanks in Column A. Is there a way to automatically number down in Column A, while skipping over the blanks and continuing where I left off? I.e. 1 2 3 4 5 ? Thanks much. Try this in A1: =IF(B1<>"",COUNTA($B$1:B1),"...

Highlighted Cell
Hi all If I highlight A1 is there a way to have the cell fill colour yellow, So I can see where I am better, when I move to say A8 etc. A1 go's back to its normal colour and the new cell is yellow. Thanks in advance Dave See if this helps: http://www.cpearson.com/excel/RowLiner.htm -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Dave" <tuco@tuco.karoo.co.uk> wrote in messag...

Slope from a range of cells
How gan i get the slope from a range of cells refered to a desire date? This is what I have in excel: ColumnA A B C D 10/17/2009 54 84 15 75 10/17/2009 58 46 48 65 10/18/2009 58 48 78 75 10/18/2009 46 78 45 46 10/18/2009 23 76 85 75 10/19/2009 12 95 95 95 10/20/2009 11 64 84 84 I want to get the SLOPE from Column B only for the dates: 10/18/2009 and 10/19/2009? Is it possible? how can i do it? -- asdf What do you mean by SLOPE in this case? I think of...

Is there a way to "glue" a matrix rectangle of cells together?
The cells would only be moved as a group, but could be detached from each other at some point. Dshope - One way: First select the rectangular range of cells. Then move the cursor near an edge of the selection. When the cursor turns into a four-arrow shape, click and drag the entire range to a new location. - Mike http://www.MikeMiddleton.com "Dshope" <Dshope@discussions.microsoft.com> wrote in message news:94B03F9D-84F2-44E3-8DE7-5A5992C6EEAD@microsoft.com... > The cells would only be moved as a group, but could be detached from each > other at s...

Text color change in a cell depend on the cursor position elsewher
Excel. I want either the background color or the text color of a cell, or cells, to change when the cursor is in a particular position. e.g. The background/text color change for the far left cell of a row when the cursor is anywhere in that row. Hi! Two things: 1. You can get a cell to change colour with a VBA routine such as: Private Sub Worksheet_SelectionChange(ByVal Target As Range) r = Target.Row Cells(r, 1).Interior.ColorIndex = 6 End Sub This paints the first cell in the row yellow. But you'll need to give some thought as to whether/how you will dea with moving off that ro...

Next sequential number on a different form
Is there a way to do this: I have a form called "List of Proposals Written" that is listed by th Proposal number. Is there a way to display on another excel spreadsheet, the mos current Proposal number? I know Access you use something like Dmax(ProposalID)+1 What should I do in Excel:confused: Any help would be greatly appreciated! Michae -- Message posted from http://www.ExcelForum.com "daniels012 >" <<daniels012.1b8pra@excelforum-nospam.com> wrote > I have a form called "List of Proposals Written" that is listed by the > Propos...

How to one cells variables as a serie to a column
I cant find the way out. I have a daily variable data in a single cell (say in A1) ant I want to form a serie with using daily values of that cell. (Variable) Date Value 21.12.2006 400 (Series) 01.12.2006 300 02.12.2006 350 .... .... 20.12.2006 420 21.12.2006 400 "noyau" wrote > That is first step. Thnx a lot. You're welcome. Glad it helped. > Now the point is to do the same thing for a > daily variable next to the date. > The problem is that the second variable is > not increasing or decreasing. > Still working but no result. ...

Error: Too Many Different Cell Formats
All, Recently, while working with a large Excel 2000 workbook, I encountered an error. The error read "Too many different cell formats." After clicking "OK" I saved and closed the workbook. Unfortunately, now, my workbook will not open!!! Microsoft's fix posted on their website is to reduce the file formats in the workbook. However, once you've closed the workbook, it is impossible to open it again. Does anyone know of a fix for this problem. My last backup file is over two weeks old!!! Thanks, Mike Some people have reported success opening the fi...

how to draw a number with DrawString?
Hi, In GDI+, this works fine: WCHAR* labelSouth( L"�S"); g.DrawString( labelSouth, -1, &latLabelFont, pointF, &latLabelBrush); but I cannot get it to draw an int or double. What I need is this int i=30; WCHAR* labelSouth( // missing conversion from int to WCHAR* here g.DrawString( labelSouth, -1, &latLabelFont, pointF, &latLabelBrush); I tried various things but I can't figure it out. I'm sure I'm missing something obvious. Thanks for any suggestion. Vit Planocka Use swprintf Function to format string and use this string=20 into DrawString Fun...

conditional format if cell contains link to another cell
Hello, is it possible to format a cell conditionally in such a way, that the content appears in a certain format if the cell contains a value or text and in another format if the cell contains the address of another cell? Thank you very much in advance, Marcel hi, Marcel ! > is it possible to format a cell conditionally in such a way > that the content appears in a certain format if the cell contains a value or text > and in another format if the cell contains the address of another cell? 1) for a contitional formulae to format a cell if it's contents is value/text [I gu...

Isolating all cells by last digit
Hi. I'm using Excel 2003 and have a small spreadsheet (one column) with thousands of entries. I need to isolate or group entries based on the last digit. Here's what I have: 2566 1231 5876 2540 4561 2960 Here's what I would like: 2540 2960 1231 4561 5876 2566 Any ideas on how to do this? TIA John John, I think you'll need a helper column. Presuming your stuff is in column A, put either of these in another column, and copy down with the fill handle: =MOD(A1, 10) =RIGHT(A1, 1) Now sort the table on that column. You can hide it when you don't need it. -- Earl...

Columns in letters vs numbers
For an unknown reason, all my columns are now designated in numbers rather than letters. What can I do to get them back to letters? Why are all my old files also coming up this way? Hi This is an Excel setting which is not workbook dependent. Go to Tools/Options/View /General and uncheck R1C1 reference style -- Andy. "garyflood" <garyflood@discussions.microsoft.com> wrote in message news:E863AB52-8A7A-4BAA-B5B3-95D823EA200B@microsoft.com... > For an unknown reason, all my columns are now designated in numbers rather > than letters. What can I do to get them ba...