Using OFFSET formula to create dynamic print area when cells contain formulas?

[umasirhc]

Good afternoon,

Looking for help.  I'm trying to set up a dynamic print area using OFFSET but, for the sheet I'm trying to print, all cells contain formulas so I've used IF and IFERROR to make them show as blank when necessary.  What I'm finding is that trying to use COUNTA to define the range of rows doesn't work because Excel considers a cell containing a formula not blank, even if visually it is.  I kind of got COUNT to work by adding a number to the column header for a column that will contain number values, but for some reason it cuts off the last line that I would have expected to print.

The data in the sheet should grow and shrink based on the values in the PRODUCT_DESC field that are pulling in from a pivot table in another file.

The formula I'm using for the dynamic print area is =OFFSET($A$1,0,0,COUNT($F:$F),12)

What am I doing wrong and is there a better way to format the formula?  I've attached the sheet, but I had to break the links since they link to protected data, so consider that all cells in columns A through L would have a background formula (except for E and H which are manual entry fields).

Thanks for any help you can provide!

Chris A.

[#2]

@umasirhc , Since you have the data in an Excel table, you don't have to do any math calculate the dynamic range. Delete the rows that are empty. When you add new items, the calculations will automatically get filled in. You can select the entire Table and set the print area and it will automatically resize.

[#3]

@umasirhc 

You formula counts all rows not exactly since cells have formulas, but since they are not blank. Any formula returns some value, empty string returned is also the value of text type.

To count cells ignoring cells with empty texts you may use something like

=SUMPRODUCT(--(F:F<>""))

[#4]

@umasirhc 

Assuming the column B has a formula which returns the text values in your actual file, then you may use the following formula for the named range...

=OFFSET(Charges!$A$1,0,0,COUNTIF(Charges!$B:$B,"?*"),12)