Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 09-07-2018, 02:40 PM
Marcia's Avatar
Marcia Marcia is offline Auto Numbering of data except blanks starting from number n Windows 7 32bit Auto Numbering of data except blanks starting from number n Office 2007
Expert
Auto Numbering of data except blanks starting from number n
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default Auto Numbering of data except blanks starting from number n

I spent hours finding the formula for auto numbering of column A of several linked sheets starting from a given number but I always got error messages after a blank cell. I do not know where to position the starting number in the formulas that I tried. If I type 58 on say Cell A34 of sheet 1, the rest of the numbers down the rows of the active sheet and other sheets should follow. There should be blank in the Number column if the data in Column B is blank. Please help.
Sheet 1
A-----------------------------B
No.--------------------------Data
58---------------------------Red
---------------------------
59---------------------------White
---------------------------
---------------------------
60---------------------------Red
Sheet 2
A----------------------------B
No.--------------------------Data
61---------------------------Apple
---------------------------
62---------------------------Lemon


63---------------------------Orange
Sheet 3
A----------------------------B
No.--------------------------Data
64---------------------------Cow
65---------------------------Carabao
Reply With Quote
  #2  
Old 09-07-2018, 06:13 PM
Logit Logit is offline Auto Numbering of data except blanks starting from number n Windows 10 Auto Numbering of data except blanks starting from number n Office 2007
Expert
 
Join Date: Jan 2017
Posts: 529
Logit is a jewel in the roughLogit is a jewel in the roughLogit is a jewel in the rough
Default

What formula are you using ?
Reply With Quote
  #3  
Old 09-07-2018, 06:26 PM
Logit Logit is offline Auto Numbering of data except blanks starting from number n Windows 10 Auto Numbering of data except blanks starting from number n Office 2007
Expert
 
Join Date: Jan 2017
Posts: 529
Logit is a jewel in the roughLogit is a jewel in the roughLogit is a jewel in the rough
Default

.
Here is a VBA approach :

Code:
Option Explicit

Sub numcells()
Dim i As Integer
Dim x As Range

i = Range("C2").Value

For Each x In Range("B2:B100")
    If x <> "" Then
        x.Offset(0, -1) = i
        i = i + 1
    End If
Next
End Sub
Attached Files
File Type: xlsm Number NonBlank Rows.xlsm (15.9 KB, 12 views)
Reply With Quote
  #4  
Old 09-07-2018, 08:32 PM
NoSparks NoSparks is offline Auto Numbering of data except blanks starting from number n Windows 7 64bit Auto Numbering of data except blanks starting from number n Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 831
NoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really nice
Default

another possibility:


assuming the 58 in your example is in A34, try this in A35 and drag down
Code:
=IF(B35="","",MAX($A$34:$A34)+1)
don't know where the first number on sheet 2 would be, but something like this in the cell should get the next number
Code:
=MAX(Sheet1!$A$34:$A$500)+1
Reply With Quote
  #5  
Old 09-08-2018, 01:27 PM
Marcia's Avatar
Marcia Marcia is offline Auto Numbering of data except blanks starting from number n Windows 7 32bit Auto Numbering of data except blanks starting from number n Office 2007
Expert
Auto Numbering of data except blanks starting from number n
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

Thank you, but the formula returns numbers that automatically start at 1, it should be 59, the next number after 58. I have copied the sample made by Logit with a macro. I would prefer a formula if it could be done please.
Attached Files
File Type: xlsm Number NonBlank Rows (2).xlsm (25.6 KB, 7 views)
Reply With Quote
  #6  
Old 09-08-2018, 02:18 PM
Marcia's Avatar
Marcia Marcia is offline Auto Numbering of data except blanks starting from number n Windows 7 32bit Auto Numbering of data except blanks starting from number n Office 2007
Expert
Auto Numbering of data except blanks starting from number n
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

Please read my previous message, in reply to your formula. I have attached the sample data. Thanks.
Reply With Quote
  #7  
Old 09-08-2018, 02:26 PM
Marcia's Avatar
Marcia Marcia is offline Auto Numbering of data except blanks starting from number n Windows 7 32bit Auto Numbering of data except blanks starting from number n Office 2007
Expert
Auto Numbering of data except blanks starting from number n
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

The macro doesn't work if I add another month with separate serial numbers in sheet 1.
Reply With Quote
  #8  
Old 09-08-2018, 02:29 PM
Logit Logit is offline Auto Numbering of data except blanks starting from number n Windows 10 Auto Numbering of data except blanks starting from number n Office 2007
Expert
 
Join Date: Jan 2017
Posts: 529
Logit is a jewel in the roughLogit is a jewel in the roughLogit is a jewel in the rough
Default

Quote:
Originally Posted by Marcia View Post
The macro doesn't work if I add another month with separate serial numbers in sheet 1.
That's because your post didn't mention anything about additional ranges where you would be starting over with a new number.
Reply With Quote
  #9  
Old 09-08-2018, 02:37 PM
Logit Logit is offline Auto Numbering of data except blanks starting from number n Windows 10 Auto Numbering of data except blanks starting from number n Office 2007
Expert
 
Join Date: Jan 2017
Posts: 529
Logit is a jewel in the roughLogit is a jewel in the roughLogit is a jewel in the rough
Default

.
Provide a nun working example of your spreadsheet, how you envision it will be when all is completed. If you have additional ranges that will begin with a different number, be certain you show that on the worksheet.

If there will be different ranges in different columns, show those as well.

It is best to provide all of the information to begin with, rather than provide it piecemeal. Something you determine is needed after receiving a working solution may very well interfere with the first provided solution - as has occurred with your last post.
Reply With Quote
  #10  
Old 09-08-2018, 02:41 PM
Marcia's Avatar
Marcia Marcia is offline Auto Numbering of data except blanks starting from number n Windows 7 32bit Auto Numbering of data except blanks starting from number n Office 2007
Expert
Auto Numbering of data except blanks starting from number n
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

Remiss of me. My knowledge of macro is limited to recording of repetitious commands. I am excited when others like you introduce a macro that I could practice on in my work. I have attached the sample transactions, and added some data. I'm living in the boondocks, internet signal here is erratic.
Attached Files
File Type: xlsm Number NonBlank Rows (1).xlsm (25.5 KB, 8 views)
Reply With Quote
  #11  
Old 09-08-2018, 02:56 PM
Marcia's Avatar
Marcia Marcia is offline Auto Numbering of data except blanks starting from number n Windows 7 32bit Auto Numbering of data except blanks starting from number n Office 2007
Expert
Auto Numbering of data except blanks starting from number n
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

Quote:
Originally Posted by Logit View Post
.
Provide a nun working example of your spreadsheet, how you envision it will be when all is completed. If you have additional ranges that will begin with a different number, be certain you show that on the worksheet.

If there will be different ranges in different columns, show those as well.

It is best to provide all of the information to begin with, rather than provide it piecemeal. Something you determine is needed after receiving a working solution may very well interfere with the first provided solution - as has occurred with your last post.
I'm very sorry. I just returned the sample that you gave without saving the changes that i made. Attached is the revised file.
Attached Files
File Type: xlsm Number NonBlank Rows (3).xlsm (25.6 KB, 7 views)
Reply With Quote
  #12  
Old 09-08-2018, 03:12 PM
Marcia's Avatar
Marcia Marcia is offline Auto Numbering of data except blanks starting from number n Windows 7 32bit Auto Numbering of data except blanks starting from number n Office 2007
Expert
Auto Numbering of data except blanks starting from number n
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

Quote:
Originally Posted by NoSparks View Post
another possibility:


assuming the 58 in your example is in A34, try this in A35 and drag down
Code:
=IF(B35="","",MAX($A$34:$A34)+1)
don't know where the first number on sheet 2 would be, but something like this in the cell should get the next number
Code:
=MAX(Sheet1!$A$34:$A$500)+1
The IF MAX formula works!! I applied it on my worksheet and at first it returned 1,2,3 but when I retyped 58 on the cell above the formula. The correct numbers popped out. I am now learning the macro of it.
Reply With Quote
  #13  
Old 09-08-2018, 04:40 PM
Logit Logit is offline Auto Numbering of data except blanks starting from number n Windows 10 Auto Numbering of data except blanks starting from number n Office 2007
Expert
 
Join Date: Jan 2017
Posts: 529
Logit is a jewel in the roughLogit is a jewel in the roughLogit is a jewel in the rough
Default

.
Here is the updated macro :

Code:
Option Explicit

Sub numcells()
Dim i As Integer
Dim x As Range

    For Each x In Range("B5:B100")  '<-- Edit range as required.
    
        If IsNumeric(x.Offset(0, -1).Value) And x.Offset(0, -1).Value > 0 Then
            i = x.Offset(0, -1).Value
        End If
       
        If x <> "" Then
            If x.Offset(0, -1) = "" Then
                x.Offset(0, -1) = i
                i = i + 1
            End If
        End If
        
    Next
End Sub
Attached Files
File Type: xlsm Number NonBlank Rows (1).xlsm (18.8 KB, 8 views)
Reply With Quote
  #14  
Old 09-09-2018, 07:33 AM
Marcia's Avatar
Marcia Marcia is offline Auto Numbering of data except blanks starting from number n Windows 7 32bit Auto Numbering of data except blanks starting from number n Office 2007
Expert
Auto Numbering of data except blanks starting from number n
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

Thank you Logit for the updated macro. I will copy this to my actual worksheet and see how it goes. I am using NoSparks formula in the meantime. Could you please help in my previous post about re How to Return Data from a List into a Different Form...
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Auto Numbering of data except blanks starting from number n Return Multiple values from data except blanks with two criteria Marcia Excel 7 09-07-2018 12:38 AM
Auto Numbering of data except blanks starting from number n Delete starting number *) and delete Unneeded data in series frustrated teacher Word VBA 5 12-07-2014 06:53 PM
Auto Numbering of data except blanks starting from number n Starting page numbering after initial pages plradoff Word 3 02-15-2014 08:59 PM
Auto Numbering of data except blanks starting from number n Copying data from sheet with deleted columns creates blanks ZGreyArea Excel 1 11-20-2013 10:12 AM
Auto Numbering of data except blanks starting from number n Page numbering starting on 2 after section break pamm13 Word 1 06-22-2011 11:10 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 04:27 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft