4644 Shares

Excel with consecutive numbering in Excel. Macro?

Excel with consecutive numbering in Excel. Macro? Topic: Open parenthesis in excel
July 16, 2019 / By Briony
Question: I am creating a workbook in excel with many different pages. Each tab will represent one invoice for our customers. I would like tab 1 is labeled 1001 to and should display WO# 1001 in the correct field. Tab #2 should be labeled 1002 and display WO# 1002, etc. This workbook could eventually contain 100’s of work sheets. Is there any way to automatically enter the WO#’s or the tab names (or both) onto the worksheet? TIA!
Best Answer

Best Answers: Excel with consecutive numbering in Excel. Macro?

Alica Alica | 9 days ago
You could use the CELL function to get the worksheet name but it takes a bit of work...: For just the worksheet name: =RIGHT(CELL("filename"), LEN(CELL("filename")) - FIND("]", CELL("filename"))) If you want it with the "WO#": ="WO# " & RIGHT(CELL("filename"), LEN(CELL("filename")) - FIND("]", CELL("filename"))) EDIT: If you are using parentheses around your worksheet names and always using 4 digits then you can use the following formula: ="WO# " & MID(CELL("filename"), FIND("]", CELL("filename")) + 2, 4) To automate calculate when changing sheets: 1. Open VB editor (Alt+F11) 2. Make sure Project Explorer window is open (Ctrl+r) 3. Open editor window for workbook (double-click on "ThisWorkbook" in Project Explorer window) 4. In the editor window that appeared, enter the following code: Private Sub Workbook_SheetActivate(ByVal Sh As Object) If Application.CutCopyMode = False Then Application.Calculate End Sub 5. Close the VB editor Your worksheets will autocalculate each time you change sheets. The one exception is when you are copy/pasting (calculate command will clear copied selection so this is to keep copied selection - in this case you would need to manual calculate - F9)
👍 206 | 👎 9
Did you like the answer? Excel with consecutive numbering in Excel. Macro? Share with your friends

We found more questions related to the topic: Open parenthesis in excel


Alica Originally Answered: Way too big for Excel. What do I do instead?
Excel is limited to 65535 rows (an probably columns). For large amounts of data, it becomes very slow: you must understand that ANY change, in ANY cell, require checking EVERY other cell to see if it is not affected by the last change! The solution? Definitely a database! (And not access: also very slow!) You could use MSSQL and IIS, but that is Miscrosft and expensive... I would suggest to use mysql, with php. Of course, this means learning these two as well... To install it: Go to http://apachefriends.org and download XAMPP (free). Install it following instructions to the letter. When starting it, start apache first, then mysql. Your database is available when clicking "admin" in front of mysql active. Mysql handles VERY large amounts of data, VERY fast (a search in a 3 million records takes milliseconds...)

Tyrrell Tyrrell
Although there is not a command to insert sequence numbers in Excel, you can insert sequence numbers in contiguous cells with clicking and dragging mouse. Step 1: Enter 1 in a cell, and enter 2 in a cell beside the first cell. Step 2: Select both the cells, and drag the fill handle Description: Selected cell with fill handle across the range that you want to fill. With dragging this fill handle, you can also fill the column of cells with sequence numbers such as these formats: “000-001,000-002,000-003…” and the sequence numbers with prefix “KTE-0001, KTE-0002, KTE-0003… ”. Notes: 1. This way is only able to insert sequence numbers in contiguous cells. See Sample Below:
👍 80 | 👎 5

Rikki Rikki
This is probably the worst way to manage invoices. At least use Microsoft Access and build a customer order database. The invoice would be a query that is turned into a report. You could then easily tally a wide range of metrics.
👍 72 | 👎 1

Mervin Mervin
I followed your direction and was able to copy the sheets how you suggested (which is great!(, however, now I am not getting a unique number for each sheet, I am getting “WO# (1001) on each sheet? There must be something else that needs to be adjusted in the formula? ="WO# " & RIGHT(CELL("filename"), LEN(CELL("filename")) - FIND("]", CELL("filename"))) By the way, thanks so much for the tip on copying sheets! It will be very helpful in the future! You guys are awesome
👍 64 | 👎 -3

Mervin Originally Answered: How can I use excel to do this?
Not sure this is exactly what you want to do but... here's a How To on how to link up spreadsheets to a master spreadsheet in Excel over at Microsoft: http://office.microsoft.com/en-us/excel-... ~ Cat ~

If you have your own answer to the question open parenthesis in excel, then you can write your own version, using the form below for an extended answer.