This an Excel presentation for my management about our user story progress and what milestones have been checked off in time. I will be downloading a *.csv from our user story app (ADO) and copy it into a worksheet at the end of this presentation xls (Example.xls)
There are two worksheets (w/s) in this Example.xls:
-
The output w/s: “May 2025”:
(ex. shows the column headers and cols A thru M and the second row is where the presentation data/formulas will go. Formulas will pull data from the “raw may” w/s) -
The input w/s: “raw may”:
(ex. shows the column header row, cols A thru I and four rows of data (two user stories). This example shows the two different record types and their formats. One record type has its ID in col A (Rec A) and the second record type has its ID in col B (Rec B). If col B is blank, then it’s Rec A and take the ID from col A. If col B is populated, then it’s Rec B and take the ID from col B)
In this use case every single line of output in “May 2025” refers to a User Story that may have one or two input user story records in “raw may”. (See ID fields to use, and record types above)
While the output formulas in “May 2025” perform simple mappings from the input w/s Rec A in output cols A thru F (disregard Cols G thru L), when it gets to col M it needs to check for a second record, Rec B in “raw may”. Something like: If “raw may” Col B, has a matching ID value to that of May 2025 col A, then look at “raw may” Rec B col I for any valid date value and place an “X” in May 2025 col M; else leave a blank col M. If “raw may’s” second record does not have a matching ID in col B (or does not exist), then start a new output record in May 2025.
Thanks.
