I was implementing a dynamic table user interface as described in Professional Excel Development. Dynamic tables use conditional formatting to hide rows until they’re needed. One problem I encountered was with deleted data. Because I was basing my conditional formats (hidden rows) on whether the row above contained data, I could have hidden rows in the middle of unhidden rows. First I’ll describe the original setup, then the changes I made to fix the problem.

The conditional formatting looks like this:

The formulas look like this:

HasData: `=COUNT(D6:G6)>0`

HideRow: `=AND(NOT(A5),ROW()>9)`

HasData returns true if there’s any number entered into the row. HideRow returns true if the row above contains no data and the row number is greater than 9. I want to show a few rows whether there’s any data entered or not. I’m showing four rows to start and increasing the number of rows as data is entered.

You can see from the above table that the data in row 12 was deleted. There’s still data in row 13, but you can’t see it because the row is “hidden” due to the fact that there’s no data in 12. Of course users should not be deleting data once it’s entered. I mean, really, just enter the data correctly, in order, and according to plan and there will be no problems. I’m sure you’ll agree that that plan is a pipe dream.

Here’s what I did to get the desired behavior:

HideRow: `=AND(NOT(A5),NOT(A6),ROW()>9,C7)`

LastRow: `=COUNTIF(A6:$A$20,TRUE)=0`

Now the rows stay visible as long as there is data below them. Blank rows are formatted red to alert the user of a problem. HideRow now returns True if the row above has no data AND the current row has no data AND the row is greater than 9 AND the next row isn’t the last row with data. LastRow simply counts the number of True’s in the HasData column starting with the current row. The row turns red if all the formulas are FALSE, meaning there’s no data in the row, HideRow is False, and it’s not the last row (the open row enticing the user to enter more data). All-in-all, I’m pretty happy with the result. What do you think?

Hi Dick,

>What do you think?

Pretty Nifty!

Thanks for sharing!

RE: Dynamic Tables.

That seems a bit too complex…And it doesn’t work right if the user blanks out 2 rows in the middle.

How about:

A6 / HasData: =COUNT(D6:G6)>0 – data on current row?

B6 / HideRow: =COUNTIF($A5:$A$20,TRUE)=0 – no data on row above, current row, or any row below

condition1: =and(row()>9,$b6) – don’t actually hide row until >9

condition2: =and(not($a6),not($b7)) – show red if no data and row below isn’t hidden

Use the SUBTOTAL function…

For counting all (hidden or unhidden) of column C, it would be =SUBTOTAL(2,C:C)

For counting only unhidden of column C, it would be =SUBTOTAL(102,C:C)

You can Sum, Count, Min, Max, etc… Just search for help on the SUBTOTAL function for more information.

-Bill