It works!!!!
After several years looking for the answer to this question, I finally found a way to do this!
To create a percentage you need, of course, the values that define the percentage. In my example, data is separated into individual blocks - by manual title. And the number of chapters in each manual will change as the manual expands, leaving a problem of how to separate chapter rows of one title from the next - so the percentage of completed chapter reviews for each title is accurate.
The solution I found was to create 'Named Blocks' for each Manual Title, by highlighting the Title row and (whatever number) the next 10 blank rows, then going up to the 'Name Box' and giving that block a unique name.
I then locked the cells of each entire title row (I password every Workbook I create to prevent users from accidentally deleting/changing formulas, etc.). I went a few columns outside of the main page, labeled and entered the two percentage-base values:
1. Number of Chapters [rows under title] using: =ROWS(BLOCK_NAME)-1 (-1 to remove the Title row itself from the count.)
2. Number Completed [cells with chosen 'completed' indicator] using: =COUNTIFS(BLOCK_NAME, "Approved*)
Now the 'Percentage Complete' data is ready to source. I chose two cells in the Title row (in the main page area) to label and enter the percentage value:
3. Percent Completed using: =[Step2 Cell]/[Step1 Cell]*100
And voila!
I also used Conditional Formatting for several functions, check it all out in the new example I've added!
|