is there a way to report by Phase or Functional Area and Status

DLanca

Hello

I have a project task sheet which has several columns with data to manage a testing phase and am having issues in reporting this data in my dashboard. I have a row report that looks something like the one below. I would like to calculate a % complete by workstream based on the Status.

What I expect for results is that Workstream A is 100% complete (i.e. all 4 tests passed), Workstream B is 50% complete and Workstream C is 75% complete.

In the Row report I have grouped by Functional Area and Status, however the results on the dashboard chart widget are not what I expect. The results on the dashboard are Workstream A - 33% (4 tests of 12) - Workstream B - 33% (4 tests of 12) - Workstream C - 33% (4 tests of 12).

Is there an easy way to summarize this data in the Row Report so that the chart widget calculates the % complete for each Functional area task and Status rather than as a percentage of the total tasks (i.e. 12)?

Paul Newcome

You will have to do this on a separate sheet with formulas containing cross sheet references.

DLanca

Thanks Paul! Do you know if there are any webinars for this topic?

Paul Newcome

I am not sure if there are any webinars, but the basics would be that you would create a second sheet, list out each workstream only once in a column. Then in a second column you would use something along the lines of

=COUNTIFS({Source Sheet Workstream Column}, @cell = Workstream@row, {Source Sheet Status Column}, @cell = "Pass") / COUNTIFS({Source Sheet Workstream Column}, @cell = Workstream@row)

DLanca

Great! Thanks Paul, I will try it!

DLanca

Hello, I am getting an unparseable error when referencing another sheet in my formula below.

=VLOOKUP([End Date]@cell;{Mars Period Calendar 2024 Range 2};2)

I think this formula would scan the End Date column and for each date in the cell, it would return the corresponding period - P1 and for 01/01/24 - P2 for 01/02/24 and 03/02/24. The table is referenced to another sheet as the Mars Period Calendar which has two columns - Date and Period as per the example below.

Date Period

01/01/24 P1

01/02/24 P2

03/02/24 P2

Could you tell me if there is something wrong with this formula?

Thanks

Paul Newcome

@DLanca Try changing "@cell" to "@row".

DLanca

I changed it to @row and it makes no difference... still get the unparseable error

DLanca

I am attaching a screenshot of the formula and the sheet for the Mars Period Calendar for your information as I still cannot get this to work...

Paul Newcome

DO you normally use semi-colons as the delimiter in formulas?

DLanca

yes, because the commas do not work for me.... I don't know if that is some regional difference US and Europe?

Paul Newcome

Ok. Yes. That is a regional thing. Instead of typing in [End Date]@row, when you get to that part of the formula, just click on the cell in that column on that row.

DLanca

I am doing it like you mentioned... I did not type in [End Date]@row - I simply clicked on the cell on the row and it automatically includes [End Date]@row in the formula

Paul Newcome

And you are still getting the same error?

DLanca

sorry... just to clarify... I haven't changed anything in the formula because I am already doing it like you mentioned... clicking on the cell. So yes, still the same error

Paul Newcome

Just to see... What happens if you use commas instead of semi-colons?