Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 08-03-2021, 09:48 AM
gjergj_06 gjergj_06 is offline Force excel to add a new reference to the newly added rows without macro Windows 10 Force excel to add a new reference to the newly added rows without macro Office 2016
Novice
Force excel to add a new reference to the newly added rows without macro
 
Join Date: Aug 2021
Posts: 2
gjergj_06 is on a distinguished road
Smile 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 !!!
Attached Files
File Type: xlsx Template_Model.xlsx (10.7 KB, 8 views)
Reply With Quote
  #2  
Old 08-03-2021, 12:35 PM
ArviLaanemets ArviLaanemets is offline Force excel to add a new reference to the newly added rows without macro Windows 8 Force excel to add a new reference to the newly added rows without macro Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

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!
Reply With Quote
  #3  
Old 08-04-2021, 07:53 AM
gjergj_06 gjergj_06 is offline Force excel to add a new reference to the newly added rows without macro Windows 10 Force excel to add a new reference to the newly added rows without macro Office 2016
Novice
Force excel to add a new reference to the newly added rows without macro
 
Join Date: Aug 2021
Posts: 2
gjergj_06 is on a distinguished road
Default

Quote:
Originally Posted by ArviLaanemets View Post
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!

What If i use office 365 since my file will mostly be in sharepoint? Is that easier or it does not have any effect?
Reply With Quote
Reply

Tags
excel 2016, no vba, numbering

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Force excel to add a new reference to the newly added rows without macro Locked Form - allow rows to be added as needed. Ms. G Word VBA 14 05-05-2021 06:12 AM
Force excel to add a new reference to the newly added rows without macro Excel VBA macro to copy specific rows and delete it pourmalla Excel Programming 2 04-02-2017 12:09 AM
Force excel to add a new reference to the newly added rows without macro 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

Other Forums: Access Forums

All times are GMT -7. The time now is 01:53 PM.


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