Using Template To Produce Reports Where Number Of DataItems Changes - Advanced.

[ChloeRadshaw]

I use named ranges heavily in my generation of Excel reports. However I am not sure how to tackle a project where I have three blocks of data all of which need to be outputted on a template worksheet and which are not fixed size.

I ve specifically separated out the calculation of the rows and the way it is presented in a spreadsheet so that users can change the look of the report without any interference from me.

My thoughts at the moment are to have three big areas for the data and use formulae to refer to another sheet directly.

I can then delete all the rows with no data.

This doesn't feel right.

Is there any way to set up a better worksheet?

[mdmackillop]

You could use dynamic named ranges for your three blocks. The size of these can be checked and rows inserted to accommodate these. A searchable set of headers in the template could be used to determine where the data will be inserted, if other actions may change the layout.

[mdmackillop]

A sample