Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 02-01-2022, 07:44 AM
IamThatGuy IamThatGuy is offline Another Unique Question for managing a project plan in Excel Mac OS X Another Unique Question for managing a project plan in Excel Office 2021
Novice
Another Unique Question for managing a project plan in Excel
 
Join Date: Feb 2022
Posts: 14
IamThatGuy is on a distinguished road
Default Another Unique Question for managing a project plan in Excel

I can't use project plan, which will obviously provide an easier fix for this

I a have a simple list of IDs and task names (pls see table below). Requirements are:
  • Auto create a unique ID, which could be any number with a prefix from column C. How?
  • Once a unique ID is defined it should not change against that task name. How do I detect changes if someone does change the ID cell against an existing task?
  • New Unique ID to be assigned when a new line is inserted (with a prefix from column C)
  • Note: New line could be inserted at the bottom or in the middle of the existing list

Happy to use power query, VBA or a semi manual way of managing this process. Any guidance much appreciated

ID Task Name
1 Task 1


2 Task 2
3 Task 3
4 Task 4
Reply With Quote
  #2  
Old 02-01-2022, 11:52 PM
ArviLaanemets ArviLaanemets is offline Another Unique Question for managing a project plan in Excel Windows 8 Another Unique Question for managing a project plan in Excel 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

1. On hidden sheet, create a formula which calculates next ID (Finds largest numeric part used, and adds 1. E.g. when you have ID values from "X001" to "X010", then net ID will be calculated as "X011".);
2. Create a dynamic named value, which returns the value of ID column in currently active row in your data table (E.g. when you have activated the row with ID "X002", then the dynamic value will be "X002", when you have activated the row without ID, the dynamic value will be "".);
3. I hidden sheet, create another formula, which returns either dynamic value from p.2 when tis is not empty string, or next ID calculated according p.1. And create a named value which returns the result or formula. (Another option is to create a dynamic name directly, without 2nd formula on hidden sheet, but there may be problems with this when you use some of latest versions of Office!);
4. In your datasheet, create data validation list for ID column with named value from p.3 as source. Now, whenever you activate data validation for existing ID, the same ID will be only available selection. And whenever you activate data validation for row with empty ID, the new ID will be only available selection. And any attempts to enter manually an ID not matching the one provided by data validation, will be blocked (only workaround is pasting the ID value into ID column - obviously this is a design flaw in Excel!).
Reply With Quote
  #3  
Old 02-02-2022, 12:25 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Another Unique Question for managing a project plan in Excel Windows 7 64bit Another Unique Question for managing a project plan in Excel Office 2010
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

  1. Our answerers are looking to "help" more often than they are looking to "do an entire project for you for free".
  2. Jump into this project yourself, when you get stuck on ONE specific hurdle, post that ONE item as a forum question.
  3. Show us what you've tried in an attached sample workbook and how it's not working for you. When you're doing the work yourself, just writing out a complete example question in a forum often points you to solution before you even finish posting.
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post
Reply With Quote
  #4  
Old 02-08-2022, 12:02 PM
IamThatGuy IamThatGuy is offline Another Unique Question for managing a project plan in Excel Mac OS X Another Unique Question for managing a project plan in Excel Office 2021
Novice
Another Unique Question for managing a project plan in Excel
 
Join Date: Feb 2022
Posts: 14
IamThatGuy is on a distinguished road
Default

Quote:
Originally Posted by ArviLaanemets View Post
1. On hidden sheet, create a formula which calculates next ID (Finds largest numeric part used, and adds 1. E.g. when you have ID values from "X001" to "X010", then net ID will be calculated as "X011".);
2. Create a dynamic named value, which returns the value of ID column in currently active row in your data table (E.g. when you have activated the row with ID "X002", then the dynamic value will be "X002", when you have activated the row without ID, the dynamic value will be "".);
3. I hidden sheet, create another formula, which returns either dynamic value from p.2 when tis is not empty string, or next ID calculated according p.1. And create a named value which returns the result or formula. (Another option is to create a dynamic name directly, without 2nd formula on hidden sheet, but there may be problems with this when you use some of latest versions of Office!);
4. In your datasheet, create data validation list for ID column with named value from p.3 as source. Now, whenever you activate data validation for existing ID, the same ID will be only available selection. And whenever you activate data validation for row with empty ID, the new ID will be only available selection. And any attempts to enter manually an ID not matching the one provided by data validation, will be blocked (only workaround is pasting the ID value into ID column - obviously this is a design flaw in Excel!).
Firstly thank you for taking the time out!

I have tried to follow this in excel but I am not getting it. Is there a video I could watch on YouTube which would explain the logic here, if you know--please?
Reply With Quote
  #5  
Old 02-08-2022, 08:46 PM
spillerbd spillerbd is offline Another Unique Question for managing a project plan in Excel Windows 10 Another Unique Question for managing a project plan in Excel Office 2013
Competent Performer
 
Join Date: Jan 2016
Posts: 130
spillerbd is on a distinguished road
Default

@IamThatGuy Do you have access to Access?
Reply With Quote
  #6  
Old 02-09-2022, 02:10 PM
IamThatGuy IamThatGuy is offline Another Unique Question for managing a project plan in Excel Mac OS X Another Unique Question for managing a project plan in Excel Office 2021
Novice
Another Unique Question for managing a project plan in Excel
 
Join Date: Feb 2022
Posts: 14
IamThatGuy is on a distinguished road
Default

I could get access but the project plans are managed in MS Excel so the solution would need to be in Excel although happy to tie MS Access to it, if that is where you are going with it?
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Sharing MS Project plan with team members Rsquest Project 7 10-29-2021 07:07 AM
Can I open .mpp files in Project Plan 1? whyisntitsimple Project 0 02-19-2020 05:07 AM
Master plan and sub projects MS project 2016 runejors Project 0 09-09-2019 03:34 AM
Another Unique Question for managing a project plan in Excel Process Improvement Project Plan raminraiszadeh Project 1 04-05-2016 06:05 AM
Another Unique Question for managing a project plan in Excel My project plan keeps locking up -not responding JaneG Project 3 10-11-2014 09:38 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 01:21 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