Create a Dynamic Excel Sheet

About the Excel Sheet:- The excel Sheet is created to track new system changes “CR” performed by IT department and check if there is any system issue which might impact business users ”Testers”. The sheet will be used by 3 team members who are responsible to update the project status. Scope Of Work:- Create Dynamic excel Sheet which can calculate any delay through various departments and the outcome of the Sheet should be PPT Slide by introducing the following. • Create 5 Input forms starting from Cell (AE till AI) once the user click on the cell input form to be opened with the below fields o Issue Type (TT/PDI) Drop Down menu. o Reference Number: If above field value = TT then the reference number should start with SD and then the user can enter 5-8 digits TT number on other hand if the user selected value PDI then the reference number should start with IM_ and 5-8 Digits to be manually filled. o Pending With: Drop Down menu (CO,PI,RA,BI, DSP and Channel Ops). o Creation Date: to be default value of Today Date however user should be able to edited and it should be a calendar. o Status: Drop Down Menu (In Progress, Pending with IT, Pending with Tester, Resolved, Escalated Blocking defect). o Ageing: Auto Calculated field (Creation date - Today date) o Closure Date: To Be selected from calendar manually by the user o Brief Comment: Free Text field o Detailed Comment o 2 extra Blank Fields Once the user fill the form all the information will be filled inside the Cell and the same should be applied for all above 5 cells on other hand user should be able to click anytime at the Cell and the same form will open with the same old values and users can update the values and save the data, it will be updated except for Detailed Comment each time the user fill it. it should capture today date and the new comment. Finally the SD# Should be Highlighted with Amber if the creation date is more than 2 Days from today date and should be Highlighted in Red if the creation date is more than 5 days from today Date. • Column AD : Should concatenate all the values from AE to AI. • Column L to P: Should accept only Percentage from 0-100 (if the value is less than 75% after 5 days from Actual RFS date “Column G” then the Cell Color Should be Amber and should be Red if the value is more than 7 days. o Exceptions: to the above rule that if there is any TT with status Blocking Defect from AE to AI Column the 7 days timer should stop for Column L to P until the TT Status is resolved. Please note that this should be only applied if the TT status is blocking defect any other status will remain business as usual. • Column A: must Start With ENT_ 4-7 Numbers. • The Sheet “PowerPoint is reading the values from ” Manually filled Sheet. • All Dates Fields on the Sheet must be selected from Calendar input • Column V: Should have Automatically the average value from Column L to P • Column W: Should have Automatically the average value from Column Q to U • Column H: Should have the number of days between Column F and G • At power Point Sheet once the user click on Generate PPT Template the attached Template should be Generated However please note that each GTM SPOC Column C should have one Separate slide • Each Slide Should not take more than 6 Rows and then a new slide to be generated for the same GTM spoc in case he have more than 6. • PowerPoint Sheet Comment Will have The TT’s Reference Created on the manually filled sheet from Column AE to AI along with the latest Status and creation Date, the user should have the option to click on any TT# on the PowerPoint and Map it to the excel File to check the Full details of the TT.