Is there any technique in Excel to assign serial numbers to non adjacent or non contiguous cells in Excel. For Example in cell A1 I want to assign 1, then after 2 rows in cell A4 I want to assign 2, then in A7 I want to assign 3, and so on with the rest cells in Column A ?
Excel VBA Course - From Beginner to Expert
200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
Assigning serial numbers to non adjacent or non contiguous cells
0
Answers
0
The basic formula you might deploy is the one shown below. Enter the first number in A1, the formula in A2, and copy the formula down as far as you need. Then, if you delete a formula within the range the numbering will resume in the next cell with a formula.
[A2] =MAX(A$1:A1)+1Key is the range definition, to wit A$1:A1. Note the $ sign. It makes the first row absolute. So, when copying down, A$1 will always remain just that while :A1 will change to :A2, :A3, :A4 etc. This range always starts with the row where you entered the starting number and ends with the row above the one where the formula resides. It's from this rule that the above formula got its funny appearance.
Of course, the next question is how to skip rows where you don't want a number. Consider a simple test.
[A2] =IF(NONTEXT(B2),MAX(A$1:A1)+1,"")This formula will only insert a number if B2 has a numeric value, including a date. Consider its twin ISTEXT() and the cousins ISBLANK() and ISNUMBER().