#1
|
|||
|
|||
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:
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 |
#2
|
|||
|
|||
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!). |
#3
|
||||
|
||||
__________________
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 |
#4
|
|||
|
|||
Quote:
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? |
#5
|
|||
|
|||
@IamThatGuy Do you have access to Access?
|
#6
|
|||
|
|||
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?
|
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 |
Process Improvement Project Plan | raminraiszadeh | Project | 1 | 04-05-2016 06:05 AM |
My project plan keeps locking up -not responding | JaneG | Project | 3 | 10-11-2014 09:38 AM |