Problem with Dynamic Range

[MartinM]

I have an Excel 2003 Worksheet which works perfectly well.

It includes calculations of the type:

=SUMPRODUCT(I.Quantum*(INDIRECT("I."&E1&"."&E2)="y"))

. . . where the INDIRECT piece is construction a Range Name from separate components. E1 contains the text MLM and E2 contains the text ISA.

I am working on converting the whole sheet to use dynamic ranges as data rows are being added all the time.

The above calculation no longer works, coming up with the #REF! error.

I can get the calculation working again if I omit the INDIRECT concatenation and put in the raw Range Name, that is to say:

=SUMPRODUCT(I.Quantum*(I.MLM.ISA="y"))

How can it be that conversion from a static Range to a dynamic Range invalidates the use of the INDIRECT funtion to form the Range name in the SUMPRODUCT calculation ?

Thanks, Martin

PS Trivial, but I find the default font now used in code snippets in this revamped forum quite hard to read. Can I change that ?

[sdckapr]

I don't think INDIRECT works with dynamic named ranges. perhaps using ONE dynamic named range directly and having the columns and/or rows be part of the defining of the range instead of using multiple names will solve the problem...

If that is not clear, define more of the problem and I can provide more details...
Steve

[MartinM]

I have a very large column of numerical data (which is the thing which is expanding over time) with a corresponding column of dates.

In columns alongside the raw data the user puts a y if the data pertains to that column's heading.

The SUMPRODUCT looks at the column heading and sums the data which matches that column heading as long as there is a y in the row.

There are various other queries using SUMPRODUCT (and a couple of SUMIFs) - for instance narrowing the above down to a specific year, or time period, and supporting some time-series graphs.

Every column (the source data and dates, and the columns which classify the data with a y) is a Named Range.

I could have "hard-coded" the columns' Range Names in the SUMPRODUCT queries but used the INDIRECT method because the Worksheet is getting wider and one day I expect I'll have to provide a dropdown allowing any one selected column to be queried, graphed etc. The INDIRECT method also allows me to use a single formula which can be copied around, picking up the data it needs according to the column it is in - this reduces errors in maintaining the Worksheet.

All this was working fine until I sought to make the column Ranges dynamic, to avoid errors when new data is added. The dynamic Ranges themselves calculate and adjust correctly.

The thing which ceased to work was the use of INDIRECT in the SUMPRODUCT functions.

Subsequent investigation has shown that using any of INDEX, INDIRECT and their like fail in SUM, SUMIF and SUMPRODUCT calculations which include the dynamic Ranges.

I can live with this - its is just less tidy and more error-prone to "hard code" the Range Names in the formulae - but wondered if there is another way to achieve what I am trying to so.

I hope this rather long explanation is clear - it would be a huge amount of work to extract and sanitise the Worksheet in question but I guess I could create a sample and post that (if I can work out how to do that in this revamped forum!).

[rory]

As Steve said, INDIRECT won't work with a DNR. INDEX however should not have a problem.
If you want to use DNRs in that way, you'll need another defined name that uses the XLM EVALUATE function as that will work with DNRs.

[sdckapr]

As I said you can build it with one Dynamic named range and thus no indirect. You can use a FORMs Combobox to choose a column (this can be added directly to a chart sheet) and/or a Forms spinner. [I use both linked to the same cell which allows explicitly selecting a set with the box or browsing through them all just incrementing the spinner]. The DNR (if using OFFSET) can use (in addition to other criteria) the linked cell to define the range. Thus you can one chart with one set of ranges that can be modified with combobox to define the column.

Steve

[MartinM]

I will work on the "one DNR" approach - many thanks.

I've also written a VBA function to extract the address of a range, and might be able to use that address in the SUMPRODUCT function instead of the dynamic range itself.

In passing, INDEX does not work with dynamic ranges.

[rory]

In passing, INDEX does not work with dynamic ranges.

That's just not true.

[MartinM]

Ok - let me re-phrase that - "I cannot get INDEX to work within a SUMPRODUCT funtion which includes Dynamic Ranges".

I'll have a another go then. LoL

Meantime my workaround is to create the Dynamic Ranges within the SUMPRODUCT function rather than via Named Ranges. A little bit messier but fully functional and a great deal faster in calculation it seems.