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.
0
9/16/2008 2:31:42 PM
excel 39879 articles. 2 followers. Follow

9 Replies
732 Views

Similar Articles

[PageSpeed] 19

Try this in A1:

=IF(B1<>"",COUNTA($B$1:B1),"")

And copy down as needed.
-- 

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================


"Guest3731" <notconfusedaboutthattoday@gmail.com> wrote in message 
news:8b2d95d4-6c8b-444e-9b52-e3bd901eba5b@e39g2000hsf.googlegroups.com...
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. 


0
ragdyer1 (4060)
9/16/2008 2:46:12 PM
In cell A1, paste :
=IF(B1="","",COUNTA($B$1:B1))
then, drag down.
Regards.
Daniel
"Guest3731" <notconfusedaboutthattoday@gmail.com> a �crit dans le message de 
news: 8b2d95d4-6c8b-444e-9b52-e3bd901eba5b@e39g2000hsf.googlegroups.com...
> 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. 


0
9/16/2008 2:47:55 PM
Enter your starting value in A1
In A2 use =IF(ISBLANK(B2),"",MAX($A$1:A1)+1)
Adjust cell references if you are starting in other than A1
best wishes
-- 
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Guest3731" <notconfusedaboutthattoday@gmail.com> wrote in message 
news:8b2d95d4-6c8b-444e-9b52-e3bd901eba5b@e39g2000hsf.googlegroups.com...
> 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. 


0
bliengme5824 (3040)
9/16/2008 2:49:29 PM
On Sep 16, 9:49 am, "Bernard Liengme" <blien...@stfx.TRUENORTH.ca>
wrote:
> Enter your starting value in A1
> In A2 use =IF(ISBLANK(B2),"",MAX($A$1:A1)+1)
> Adjust cell references if you are starting in other than A1
> best wishes


Thanks for all of these helpful replies - much obliged.

0
9/16/2008 3:31:18 PM
On Sep 16, 10:31 am, Guest3731 <notconfusedaboutthatto...@gmail.com>
wrote:
> On Sep 16, 9:49 am, "Bernard Liengme" <blien...@stfx.TRUENORTH.ca>
> wrote:
>
> > Enter your starting value in A1
> > In A2 use =IF(ISBLANK(B2),"",MAX($A$1:A1)+1)
> > Adjust cell references if you are starting in other than A1
> > best wishes
>
> Thanks for all of these helpful replies - much obliged.


Hey, not to be a pest, but can this be modified so as to skip over
(not number) A1 if B1 is either blank (empty), or contains a string
containing " no " as a substring?   Thanks, if you have a minute for
this -

0
9/16/2008 5:10:43 PM
What type of data is in Column B ... text, numbers, or both?

Would you want "Nobel Prize" and "hypnotize" to be numbered or skipped?
-- 
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Guest3731" <notconfusedaboutthattoday@gmail.com> wrote in message 
news:72039652-9559-4301-be9e-623a19aac933@m3g2000hsc.googlegroups.com...
> On Sep 16, 10:31 am, Guest3731 <notconfusedaboutthatto...@gmail.com>
> wrote:
>> On Sep 16, 9:49 am, "Bernard Liengme" <blien...@stfx.TRUENORTH.ca>
>> wrote:
>>
>> > Enter your starting value in A1
>> > In A2 use =IF(ISBLANK(B2),"",MAX($A$1:A1)+1)
>> > Adjust cell references if you are starting in other than A1
>> > best wishes
>>
>> Thanks for all of these helpful replies - much obliged.
>
>
> Hey, not to be a pest, but can this be modified so as to skip over
> (not number) A1 if B1 is either blank (empty), or contains a string
> containing " no " as a substring?   Thanks, if you have a minute for
> this -
> 


0
ragdyer1 (4060)
9/16/2008 8:14:09 PM
RagDyer wrote:
> What type of data is in Column B ... text, numbers, or both?
>
> Would you want "Nobel Prize" and "hypnotize" to be numbered or skipped?

It is all text - and I guess what I would want to have skipped
(besides the blank B rows) would be "no ", case-insensitive - that is,
an "n", an "o", and some whitespace.  Does that make sense?

Many thanks.

0
9/17/2008 12:55:24 AM
You'll have to *manually* number A1 if B1 meets the criteria.

Then, enter this formula in A2, and copy down as needed:

=IF(OR(B2="",ISNUMBER(SEARCH("no ",B2))),"",MAX($A$1:A1)+1)

Since you said:
<<<"   "no ", case-insensitive - that is, an "n", an "o", and some 
whitespace.">>>

An individual "No", or "no" *will* be numbered, since there is no space 
after the "o".


-- 

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

"Guest3731" <notconfusedaboutthattoday@gmail.com> wrote in message 
news:1c15aa6b-afb5-4d03-98f3-17b10b4fd2ad@c58g2000hsc.googlegroups.com...
RagDyer wrote:
> What type of data is in Column B ... text, numbers, or both?
>
> Would you want "Nobel Prize" and "hypnotize" to be numbered or skipped?

It is all text - and I guess what I would want to have skipped
(besides the blank B rows) would be "no ", case-insensitive - that is,
an "n", an "o", and some whitespace.  Does that make sense?

Many thanks.


0
ragdyer1 (4060)
9/17/2008 3:17:46 PM
On Sep 17, 10:17 am, "RagDyeR" <ragd...@cutoutmsn.com> wrote:
> You'll have to *manually* number A1 if B1 meets the criteria.
>
> Then, enter this formula in A2, and copy down as needed:
>
> =IF(OR(B2="",ISNUMBER(SEARCH("no ",B2))),"",MAX($A$1:A1)+1)
>
> Since you said:
> <<<"   "no ", case-insensitive - that is, an "n", an "o", and some
> whitespace.">>>
>
> An individual "No", or "no" *will* be numbered, since there is no space
> after the "o".

Thanks!
0
9/17/2008 5:22:18 PM
Reply:

Similar Artilces:

Numbering
Can I enter numbers in a cross functional flowchart? On Mon, 24 Aug 2009 12:07:02 -0700, carosaam <carosaam@discussions.microsoft.com> wrote: >Can I enter numbers in a cross functional flowchart? Yes. Do you mean for the process names or function names? Yes. -- Regards, Paul Herber, Sandrila Ltd. Electronics for Visio http://www.electronics.sandrila.co.uk/ Electrical for Visio http://www.electrical.sandrila.co.uk/ Electronics Packages for Visio http://www.electronics-packages.sandrila.co.uk/ ...

Adding a row to a protected table
Graham Mayor provides a macro that is extremely helpful to me in a project I am working on entitled: "An alternative method of adding a row to a protected table " (http://www.gmayor.com/word_vba_examples.htm). What I am having trouble with is identifying the number of the table. I have a lengthy document and over time some tables have been added or deleted, and I am suspecting that I my table numbers are 'out-there.' I have a commandbutton running the above macro and if I tested the macro to add rows to tables 1-4 and it works fine. However, I cannot seem t...

How do I expand the number of characters in Access comment box?
I am using Access 2007 and want to be able to include more than 250 characters in a "Comment" box. Hi, You could try changing your table's field type to Memo. Assuming that your are talking about a field in a table, that maybe shows on a form. If that is not the case, please describe precisely what comment box your are attempting to use. Clifford Bass Pearlene wrote: >I am using Access 2007 and want to be able to include more than 250 >characters in a "Comment" box. -- Message posted via http://www.accessmonster.com ...

negative number to positive number
How can I change a negative number to positive number Multiply by -1 or use the ABS() function. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "fgiord" <fgiord@discussions.microsoft.com> wrote in message news:62C2A6DA-7AC2-4379-AEFC-B3D6F3698E58@microsoft.com... > How can I change a negative number to positive number =ABS(##) or mult it by -1 -- Regards Rob "fgiord" <fgiord@discussions.microsoft.com> wrote in message news:62C2A6DA-7AC2-4379-AEFC-B3D6F3698E58@microsoft.com... > How can I chan...

Hide page numbers for mutliple discontiquous levels
Hi: I need to hide the page numbers for levels 1 and 7 in my TOC. I used the \n switch to hide level 1, but cannot find a way to specify multiple ranges for this switch. Is there a way to do this? I am using Word 2003. Phil You have to use a trick. See the "Omitting page numbers for noncontiguous levels" section of http://sbarnhill.mvps.org/WordFAQs/TOCTips.htm#OmitPageNumbers -- Suzanne S. Barnhill Microsoft MVP (Word) Words into Type Fairhope, Alabama USA http://word.mvps.org "Phillip Llacuna" <phillip.v.llacuna@lmco.com> wrote in me...

1-52 pages, 8,000 employee numbers
Dear Excel, I have the ultimate challenge: to do job books for 8,000 employees. The form is completed, but the request is to make booklets of 52 pages for each employee. Each page of the booklet must also have each employee's number on it (0001 to 8000). Each page o fthe booklet must be numbered with the week number from 01 to 52. Therefore I need to auto generate 416,000 pages. Ridiculous I know but nobody listens to us! How can I export the above scenario to PDF from Excel? Thanks On Apr 19, 2:17=A0pm, gatecrasherg13 gatecrasherg13 <gatecrasher...@gmail.com> wr...

Need a formula for multiple sums in a cloumn when rows vary
I am making a invoice I need a total for each truck, with a grand total at the end. The number of rows for each truck in the amount column will vary. Is it possible to have the text truck total to the left of the total? A small example below, there will be more entries under each truck and more trucks also. tr# date hrs rate amount 3 4/12/10 10 50 500 4/13/10 5 50 250 truck 3 total 750 4 4/10/10 1 50 50 truck 4 total 50 and so on ...

Sequential numbering in Number field
When I am entering transactions manually, I will put "Debit" or "VISA" to indicate how the withdrawl was made. When I download my account information, Money05 overwrites this with a sequential number. I have to go into the entry a second time to redo my change. Does anyone know how to turn this "feature" off? I want my values used - sequential numbering does not help me in the slightest. This is not a "feature" you can turn off. It's a reflection of the basic premise of downloaded transaction data. The presumption with downloading data...

Number of Cells in a column
Hi, I want to add this formula in my ESS. COUNTIF(sheet1!S2:S43,"*Yes*") to find out the number of value containing "YES". My concern is I don't have fixed count from S2 to S43. It could be something else. What can I do to have this code reusable? I thought something like COUNTIF(Sheet!S2:Count($S2),"*Yes*") \But it doesnt work Thanks for your help Jack Try this if there is nothing else in that column:- =COUNTIF(sheet1!S:S,"*Yes*") -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - ...

General Number Format Changed
Excel 2003 on XP I have a collegue who's Excel setting for General number format displays as 0.1 instead of 1 in the sample box. ie if you type in "1" you get "0.1", if you type in ".1" (as in 0.1) you get "1". Where in the settings can this General format be returned to standard? Many thanks DeanH Sorry that should have been: ie if you type in "1" you get "0.1", if you type in "1." (as in 1.0) you get "1". "DeanH" wrote: > Excel 2003 on XP > I have a collegue who's Excel setting for G...

How to define cells so that only numbers can be filled?
Hi, How could I format cells in excel so that only numbers can be filled The input should be integer e.g. between 0...99 and cells shoud no accept any other marks, such as space, letters... simply nothing els but integers. Thanks -- Message posted from http://www.ExcelForum.com Hi Juha! Try: Data > Validation You'll find it pretty intuitive and very flexible as to what you can do as far a restricting input to a cell. -- Regards Norman Harker MVP (Excel) Sydney, Australia njharker@optusnet.com.au Excel and Word Function Lists (Classifications, Syntax and Arguments) available fre...

Copy rows of data to another worksheet where ReturnDate is blank
I'm sure this is do-able within Excel but what formula I should use to accomplish it escapes me. Basically, I'm working with a spreadsheet which lists books checked out from our Resource Center. When the books are returned, the Return Date is entered in column D. I want to run a report showing everyone who has outstanding books without having to go through the spreadsheet, row-by-row, to copy the data to the worksheet "Outstanding." Thanks for your help! Try AutoFilter - Select Row 1, go to Data, Filter, AutoFilter. An arrow will appear at each cell in that row....

Numbering, revisited
While there are already a lot of posts about numbering, I haven't been able to find the solutions for my specific quandry... [BTW, I'm posting this under "General" even though ultimately I'd like to make macros to support the answer -- since a manual solution needs to be the first step! :) ] Background: My workgroup is preparing to migrate to Word 2007 very soon. We create/maintain hundreds of large procedure manuals, each containing multiple chapters (sometimes up to 40-50 per manual). Each chapter consists of steps, using multi-level numbered lists...

transaction number in PC Charge
Is there any way to manage the fields that should go to PC Charge while tendering credit cards in RMS. The problem is that transaction number ( called Ticket in PC Charge) doesn't appear in PC Charge and that cost as extra money. Regards, Ewa ...

Limit number of characters in a cell
Hi, Is there a way to limit the number of characters that a user can key into a cell. I want to get a list of names and addresses imputted by user, but I don't want them to be more than 35 characters. Thanks for the help Dr. Senji Take a look at Data|Validation. You can have excel yell at the user when they hit enter after typing in a too-long string. Dr Senji wrote: > > Hi, > > Is there a way to limit the number of characters that a user can key into a > cell. > > I want to get a list of names and addresses imputted by user, but I don't > want the...

Case, Quote numbering
We should have the ability to number the cases without having a suffix. If we do have a suffix, we should be able to define the suffix. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser/en-us/defa...

Rounding numbers to the nearest thousand
Simple question (i hope) Is there a simple way i can round numbers to the nearest thousand by using the format cells command. For example i would like to display 1,234.56 as 12 I don't want to be diving by 1000 and rounding to zero decimal places because i still want the whole number displayed in the formula bar when selecting it. i use excel 2003 thanx Mark Format the number as #,##0,;-#,##0, or similar. Not the comma at the end - this tells Excel to display as thousands (two commas here will display as millions). Note that 1,234.56 will display as 1 (thousand) not 12. "...

selected row count of list box
A2k Is there a way to get the selected row count of a list box dynamically as the user selects rows? Delphi has an event called "OnSelectionChanged" but Access is much more limited. I don't want the user to have to exit the list box or click a button or anything manual in order to see the # of rows he's selected. How can this be done? I know about "lstCusts.ItemsSelected.Count" but not sure what event to use it in to accomplish what I need. Thanks, Keith Never mind. I did this and it handles both mouse and keyboard selections: Private Sub lstCusts_AfterUpdat...

Form of phone numbers in contact list
I am using Blackberry's Desktop Software to syncronize with my MS Outlook Contacts list. The onboard caller-id function of the phone that is supposed to correlate the incoming phone number to a name of a contact (should the number be in my contacts list) isn't working. According to help from Blackberry the problem is the "form" of the numbers as they're being loaded on the Blackberry from MS Outlook. Specifically, apparently the fact that the numbers are stored in the form of (###) ###-#### instead of something like ###-###-#### is keeping the phone from realizi...

Deleting rows at bottom of sheet
I have 82 rows of data in my excel sheet but 65,000 rows in my file! How do I get rid of these unnecessary additional rows at the bottom of my sheet? I have tried some of the methods listed on this site but have not had any luck. When I select the unncessary rows and try to delete them, I am not getting the pop-up that allows you to select "entire row." Hi Bethany You can't delete the rows in Excel. But you can hide them if you want like this 1) Select row 83(Click on 83 to select the whole row) 2) Ctrl-Shift-down Arrow to select all rows below row 82 3) Right click on the s...

sorting numbers and numbers that contain text in excel
A column contains both strictly numbers and also numbers that are followed by text (e.g., row 1: 1000, row 2: 1500; row 3: 1000a; row 4: 1500a) Identical numbers are related documents, with the text suffixes referring to addenda documents; thus, document 1000 has an addendum document 1000a; How can I sort the column so in the following order: row 1 (1000), row 3 (1000a), row 2 (1500), row 4 (1500a)? Thank you -- MZ =TEXT(A1,"0") will turn each into text, then sort by that helper column (and don't accept Excel's suggestion to treat text that looks like number...

How do I update a spreadsheet with numbers input into another?
How do I link two speadsheets in order to update both at the same time? "Duma" wrote: > How do I link two speadsheets in order to update both at the same time? Probably something like this In Sheet2, In A1: =IF(Sheet1!A1="","",Sheet1!A1) Copy A1 across/down to cover the extent Sheet2 will then reflect entries/updates in Sheet1 for the formulated range -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- If both spreadsheets are the same, headers, data ranges, titles etc, basically same architecture on both sheets, then... While on Sheet1...

how do I set up a number to arabic number
I have change my office 2003 to 2007. What I want to know how to change format number in English to arabic number. Hi Ashraf, Assuming the number in in a field, simple: add an 'Arabic picture switch to the filed containing the number. For example, with page numbering- {PAGE \* Arabic } -- Cheers macropod [Microsoft MVP - Word] "Ashraf" <Ashraf@discussions.microsoft.com> wrote in message news:3080821A-C676-4FA2-88C4-D3CDCECF0193@microsoft.com... >I have change my office 2003 to 2007. What I want to know how to change > format number in English...

Importing external data in plain text row format
I have data in the following format: -------------------Begin Sample Data---------------------- Fernando E. Amuchastegui Office of the County Attorney 115 S. Andrews Avenue Suite 423 Fort Lauderdale 33301 954-357-7600 Voice 954-357-7641 Fax famuchastegui@broward.org Ronald J. Anania Law Offices of Ronald J. Anania 10910 S W 10th Court Davie 33324 954-476-9616 Voice 954-476-9693 Fax law1492aol.com Eric J. Anders Haley Sinagra Paul & Toland, P.A. 100 SE 3 Avenue Suite 1900 Fort Lauderdale 33394 954-467-1300 Voice 954-467-1372 Fax eanders@hsptlaw.com Hal B. Anderson Billing Cochran H...

page numbering #15
Can I add a suffix to a page number such as "1a" or "1.1"? Yes, View, Master page or Background, depending on the version. The numbering will have a # symbol. Put the text either before the # or after. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Banker" <Banker@discussions.microsoft.com> wrote in message news:C0708E7D-BAAB-4BD4-A940-3E7BF2C8A938@microsoft.com... > Can I add a suffix to a page number such as "1a" or "1.1"? "Mary Sauer" wrote: > Yes,...