On a once daily basis I need to import approx. ten worksheets of data (from an Access db) into an Excel
workbook and analyse this data using various array formulae into a summary page. The data imported can vary between one and
ten thousand rows per worksheet.
I am wondering if it is possible to use dynamic named ranges in my arrays to
avoid the tedious task of manually redefining the many ranges each time I download new data.
The following is an
example of one of the arrays from my summary worksheet.
sales!$G range is text (names)
sales!$H range is numbers (sale values) and
sales!$I range is a count of the sales for each salesman using the formula :
I have the appropriate formula for defining the respective
text and numerical ranges (from other posts on this forum) and have tried using the range names in the above array, without
success. The array holds up when I use the range name for sales!$G in the COUNTIF calculation but when I put it in the
AVERAGE formula I get an N/A! error.
Is it possible/practical to use dynamic named ranges in this manner and if so
what additions/changes to syntax are necessary or would I be better advised just defining static ranges which exceed my
likely requirements in each case?
Thanks in advance for any help and apologies if this post would be better suited
to another forum.