LinkBack URL
About LinkBacks
Adding a sample file as requested - thx
Adding a sample file as requested - thx
Last edited by rz6657; 08-21-2018 at 11:23 AM. Reason: Adding another sample file
I was thinking about another way. Each project will end up with 4 rows depending on the # of deployments - what if:
1. Via a macro scan each row if project X on column V has the DEPLY_PATHMSID on the same row with an ID, copy that row to Dep1 tab and delete that data on that row on the main Milestones tab
a. if for the same project X on the row below it also has the DEPLY_PATHMSID on the same row with a unique ID, copy that row to Dep2 tab and delete that data on that row on the main Milestones tab
b. if for the same project X on the row below it also has the DEPLY_PATHMSID on the same row with a unique ID, copy that row to Dep3 tab and delete that data on that row on the main Milestones tab
c. if for the same project X on the row below it also has the DEPLY_PATHMSID on the same row with no unique ID (null), do nothing
2. Now, after the macro completes this correctly, I have use VLOOKUP to lookup the values I need per each of the 3 deployments for the same project on 1 row in the TechDev tab... I added another file ---
Adding file
If VLOOKUP is not the right approach, is there another option to get this to work? Any ideas and help is appreciated --- thx
Working with the file attached to post #1
1) A helper cell showing the deployment ID's is inserted into each deployment section on the 'TechDev' sheet
Note that the helper cells may be hidden for aesthetic purposes.
2) the helper cells are populated using*:Formula:
Please Login or Register to view this content.
3) The remaining cells of each deployment section are populated using Index/Match/Match formulas similar to:Formula:Please Login or Register to view this content.
Note that each deployment section is conditionally formatted so that zero values are hidden.
*Denotes an array entered formula which is confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
Note that an array entered formula was used as there is no Excel version shown in the profile. If version 2010 or later is used a regular AGGREGATE based formula could be substituted.
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
Hi JeteMc - this works well; however, it will only work for the 1st project. If other projects have multiple deployments, it will only repeat the 1st deployment IDs, not the new set of deployment IDs for the new project... When I changed the $BI$3:$BI$6 to $BI$7:$BI$10, than it identified the correct 1st deployment ID for project # 2... If I just changed the 6 to 100 so that it covers multiple rows, it repeats the 1st deployment ID... I'm following your instructions to the tee --- thanks so much for taking time to look into this ---
If you could upload a sample that has two or three projects with multiple deployments, we may be able to find a resolution to the problem.
Let us know if you have any questions.
I've uploaded another sample file: Multi deployments(rz6657) (2).xlsx in 1st post ---- thx
Made a few modifications to the formulas and they now appear to work.
Helper column array entered formula:Formula:Please Login or Register to view this content.
Formula to fill table:Formula:Please Login or Register to view this content.
Let us know if you have any questions.
WOW! it does work so beautifully ---- thanks so much JeteMc--- you have saved me much work ---
You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.
JeteMc--
I'm back asking for some assistance. It's the same type of obstacles; however, they've revised the way the report used to provide the info and it now requires me to relook at the formulas and another solution. I've attached a sample file and the challenges I'm facing. Your help is greatly appreciated. Thanks
The goal is to get the target and actual dates for each milestone--- I forgot to add the end goal --
The following formula seems to work:Formula:Please Login or Register to view this content.
Note that the formula has only been applied through row fifteen.
Note that I have only checked a few values, so rigorous checking is suggested.
Let us know if you have any questions.
JeteMc--
You are on the right path. I may have left out critical details; therefore, I've added notes to the Notes tab and some samples. Please let me know if you have any questions. Thanks so much for your help.
Let's see if these are better:
1. For the 'Agile' section:Formula:Please Login or Register to view this content.
2. For S:X and CM:CRFormula:Please Login or Register to view this content.
3. For Y:CKFormula:Please Login or Register to view this content.
Note that the Milestones!$AF$5:$AF$1254=1 portion of formula #3 references a helper column on the Milestones sheet and that the 1 is changed to a 2 for the second deployment, 3 for the third deployment etc.
The helper column, which may be moved and/or hidden for aesthetic purposes, is populated using: =COUNTIFS(H$5:H5,H5,Z$5:Z5,Z5)
The 1/1/00's are zeros formatted as dates. I have hidden some using conditional formatting (font color = fill color). That is probably caused by cells on the Milestones sheet that display as blanks but are actually zeros.
Let us know if you have any questions or find any issues.
JeteMc - again you came through. I really am appreciative for your contribution on this site. Thanks again!!!
You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks