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!

Attached Files

AEC Bookplan (777-200) TEST_ver2.xlsx (17.8 KB, 4 views)

I don't understand.
You wrote: "What I need is to count the number of rows entered between each Title, so I have a value to calculate the percentage of completed chapter reviews from."

And in the file you entered the data so that each title has exactly 10 rows.
Does each title always have 10 towers? If so, why manually create 10 NAMEs and manually enter formulas 10 times in M7, M18, M29, ...?
You simply always have 10 rows for each title.

Besides, if you have 50 titles, do you also manually create NAMEs 50 times and enter formulas 50 times in columns M and O (50*2 = 100 times)?

If the number of rows between titles in column B changes for each title, then:
1. If you want to count the number of empty and non-empty rows between titles, then:

Formula in M7 (file in #6)

and copy down to the last title line

2. If you want to count the number of non-empty lines between titles, the formula is given in post #2.

In this case, the formula for M7


I think you want point 2.
----------------
Formula for O7 (note: "Approved" is in column E - file in #6)

-------------------
Note:
1. If the formula causes an error, try changing ";" to ","
2. There must be at least 1 non-empty cell after the last title in column B. In the file with #6, cell B130 <> "". If there are only empty cells from B122 down, you need to enter B122 = "hic hic hic" or whatever

Hey, Batman1! Thanks for helping out with this, I really appreciate it

This example is strictly the template (and example) for this workbook. I created 10 blank rows under each Title row simply for data uniformity during testing and then blanked each row to create a sanitized example to post here.

The number of titles in Column B will (only) increase depending on the number of manuals created as the project moves forward.

The number, composition, and details of chapters for each manual created (Columns C~H) should (only) increase depending on the number of manual chapters created as the project moves forward.

e.g., There can be Manuals with anywhere from many to just a few Chapters.

Again, I had been looking for a solution to this for a while, and there are now several workbooks the company wanted created using the same 'titled blocks of data' method.

I will try this with your formulas now!

Thanks, again! I'll let you know how it goes!