#1
|
|||
|
|||
Force excel to add a new reference to the newly added rows without macro
Hello
I would need some help with an excel file I am working on. The calculations in the file are quite straightforward but since I am going to use this file for almost everyday I will be adding new lines and deleting old ones but I would like to avoid manual work as much as possible. I had two problems which one of them I solved after digging up in web. My current problem is that I cannot find a way to make excel automatically add a row name (or reference) when I add a new row or in simpler words: ""Numbering of existing fields in the file and numbering of the newly added ones should be assigned automatically""" I already managed to find a solution for the excel to automatically sum a new row if we add one using the offset function. Basically, I have a certain number of categories and each category has subcategories. Each subcategory has a specific reference for example: PW1. The subcategories for this would be: PW 1.1; PW 1.2 .... and so on. Is there a way without using VBA to make excel add this reference in the moment that a new row is inserted? For example, If I want to add A9 or A10 as new rows (in the file attached), is there any way to automatically make excel put like PW 1.2.4 ; 1.2.5 in column A (A9, A10)? Thank you very much !!! |
#2
|
|||
|
|||
It isn't reasonable to use formulas for generating an unique ID for entry, because you cant create such ID without it being dependent on record position. I.e. whenever user sorts the table, all ID's are changed - and your ID will be meaningless!
Probably is possible to create some VBA procedure which generates such ID whenever a new entry row is created, but it will be prone to faulty results (it's practically impossible to control in which order are cells filled when a new entry is filled, and which row in your table will have the new entry). I myself prefer the approach, where: On hidden sheet, the next ID is calculated - preferably a dynamic one (e.g. when in your table active row has ID, the next ID is equal to it, when ID field for active row in your table is empty, ID with MAX value is read from tables ID column, and 1 is added); The cell where next ID is calculated is defined as Name; In your table, you define Data Validation list for column where entry ID's are entered, with this defined Name as source. Now when the row has ID, the user can select only existing ID, but when ID field is empty, the user can select only predefined new ID. And for all other formulas, which use data from your table, you can ignore rows where ID field is empty! |
#3
|
|||
|
|||
Quote:
What If i use office 365 since my file will mostly be in sharepoint? Is that easier or it does not have any effect? |
Tags |
excel 2016, no vba, numbering |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Locked Form - allow rows to be added as needed. | Ms. G | Word VBA | 14 | 05-05-2021 06:12 AM |
Excel VBA macro to copy specific rows and delete it | pourmalla | Excel Programming | 2 | 04-02-2017 12:09 AM |
Restrict Editing but Allow New Table Rows to be Added | weavie27 | Word Tables | 3 | 05-02-2014 03:11 PM |
Word counting macro for newly added portion? | New Daddy | Word VBA | 24 | 09-30-2013 07:30 PM |
Funky connector behavior with newly added anchors | koz | PowerPoint | 0 | 12-05-2012 01:28 PM |