Help I need formula help please
I'm looking to populate the date in my active sheet named "Project Tracker". cell AM8 based on the drop down from cell AH8 (image 1). The data with different timelines is on a 2nd sheet called "T-Structure" (image 2). I want the sheet Project Tracker, cell AM8 (image 1) to look up based on the dropdown in Project Tracker, cell AH8 and the category on project tracker, cell AM3 the value of the cell that matches on the T-Structure which would be cell D4 on T-Structure (image 2). The cell D4 then needs subtract from the Project Tracker, Estimated Completion Date, which would be cell AJ8 to return the FINAL date in cell AM8.
1.png
2.png
It's complex because the Project Tracker sheet has many categories, which I have formulas to let me know if a particular category is in scope based on a drop down on Project Tracker, cell C8. Base on the drop downs in C8 the sheet enters across N/A into categories that aren't in scope. Below is the existing code already in the cell. I'm looking for the existing code to work with the above request.
Here is the current code in AM8. Again, only looking to add in the above request into this code somehow to make it all work. Lookup the corresponding number from the matrix on T-Structure to provide date on Project Tracker but also keep the code to make the sheet dynamic in AM8.
Current Code in AM8
=IF($C8="","",IF($C8="Kickoff, Panel Replacement, Door Alarm Remediation, Integration, Training & Go Live",$AJ8-$AM$5,IF($C8="Kickoff, Panel Replacement, Integration, Training & Go Live",$AJ8-$AM$5,IF($C8="Kickoff, Door Alarm Remediation, Integration, Training & Go Live",$AJ8-$AM$5,"N/A"))))
Note - Project Tracker Table Range is A3:FL146
T-Structure Table Range is A3:EC12
Dropdowns from Project Tracker, cell AH8 are on T-Structure Sheet, A4:A12
Thank you to anyone who can figure this out for me.
Regards,
Kevin
Bookmarks