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?