Jan 27, 2012. Excel formula for auto generating record numbers - I am trying to auto generate numbers for a group of records that follows a sequence, ie: record one is ATT0001, record two is ATT0002 and so on but ca. I want to assign a macro on a button to generate a customize serial number in a specific cell, check last serial number and add 1 when click on the button on my saved datasheet, Column A is where my serial numbers see format bellow, then next column is another category,. Excel Dates How to Work with Dates Before 1900 in Excel If you work with dates prior to 1900, Excel offers little direct help. However, some Excel formulas and a free. How to Generate a Number Series in MS Excel. It's easy and fast to create a number series in Microsoft Excel. Understanding how to use the spreadsheet correctly to generate an automatic number series will save you a lot of time, especially.

• • • • • Short cut way to create serial numbers 1 to 5000 • Become a (free) to remove the ad that appears in the top post. • If this is your first visit, be sure to check out the by clicking the link above. You may have to before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. • Click to reset your password. You MUST have a valid e-mail address so that you may receive the instructions to complete the reset. Hello folks, I am looking for a short cut way to populate serial#'s 1 to 5000 starting cell A1 to A5000 assuming that coloum B, C, D all have 5000 rows of data.

Note column A is totally blank. The current technique I use to create 1 to 5000 serial#'s is as. (i) I type 1 in cell A1. (ii) I copy cell A1 (Cntr C) (iii) Then, I hold Cntrl + Shift down and press 'END'.

Doing so higlights all the data in the spreadsheet uptill 5000 rows (iv) I, then, remove my finger from 'Cntrl' while still holding down the 'Shift' key and at the same time using left arrow to unhiglight the other data columns such that only columnA remains highlighted i.e. (v) Now, I hit ENTER key. Doing so copies 1 from Cell A1-A5000.

(vi) Now, I go back to cell A2. Type 2 in it.

(vii) Now I higlight both cell A1 & A2 together (viii) Once both cells are higlighted, using my mouse I double click the fill handle. Doing so populates all the serial#'s from 1 to 5000. I use this technique very frequently. As you can see there are 8 steps I follow to accomplish this simple task.

However, I always try to learn new and improved way of doing things. I wonder if there is any one out there who either knows how to accomplish it more qucikly OR can be accomplished with less then 8 steps listed above. Jindon / Norie, thanks for your tricks. Jindon, I liked typing 1 in A1 and holding cntrl key to fill it down. I noticed, when I clik my mouse to fill down while holding cntrl key, it stops filling down at the cell where the adjacent cell (i.e. Cell in column B ) is empty.

So what would be solution here. How can I have the fill handle continuously keep filling down without stopping at cells with adjacent empty cells.

If this works out it would be the best trick. Drivers Pcchips P17g Windows Xp. Norie, I tried yours. It was exactly 7 steps from A1 to A5000. 1 Step shorter than mine. Thanks to both. Assuming that coloum B, C, D all have 5000 rows of data Since you do this regularly, why not record your steps with the macro recorder and attach the macro to a toolbar button.

That way, in future, the whole process could be achieved with a single click. If you try this but get stuck, post back with your recorded code and we should be able to help you out and tidy the code. BTW, I think you could save a few key strokes with your current method: (i) Select A1 (no typing) (ii) Ctrl+Shift+End (iii) Shift+Home (Should now have A1:Aend selected) (iv) Type 1 and confirm with Ctrl+Enter (puts 1 in all cells). Now continue from your step (vi).

Libby wants to insert a row number into column A of her worksheet. She wants the column to reflect the correct number of the row, even when she adds or deletes rows. There are any number of formulas you can use in column A that will return a row number. Perhaps the easiest is to use the ROW function, like this: =ROW() This formula returns the row number of the cell in which the formula appears.

Internet Download Manager Link Ile Video Indirme there. If you want to offset the row number returned (for instance, if you have some headers in rows 1 and 2 and you want cell A3 to return a row value of 1), then you can modify the formula to reflect the desired adjustment: =ROW()-2 Of course, the ROW function isn't the only candidate for your formulas. You can also use a formula that actually examines the contents of the adjacent column (B) and return a row number only if there is something in that adjacent cell. ,') This formula, placed in cell A1, examines the contents of cell B1.

If there is something there, then the COUNTA function is used to count the number of occupied cells between cell B1 and whatever cell is to the right of where this formula is placed. The formula also places a period after the row number that is returned. Make sure the dollar signs are included, as shown, and then copy the formula down as many cells as necessary to create your row numbers. The advantage to a formula such as this one is that it checks to see if something is in column B before it returns a row number. This means that you can copy the formula down beyond the actual end of your data rows, and only those rows that have data (triggered by something in column B) will have a row number. The same sort of technique could be used with the ROW function instead of the COUNTA function: =IF(TRIM(B1)',ROW()&'.'