How To Use Indirect Function Within Sumproduct

[modytrane]

I have the following formula, which works; however I need to make it dynamic.

Please Login or Register  to view this content.

This formula resides on worksheet "Report".
The reference to ranges Input!$F$2:$F$8250 and Input!$G$2:$G$8250 will change based on various other factors. The coulmn reference remains constant. The row references 2 and 8250 needs to be dynamic. These values are available on the Report sheet [where the formula resides] on the same row [where the formula is] in columns N and O.

For example:
If the formula is in G15, then N15 may be 34 and O15 may be 96.
and my formula should look like:

Please Login or Register  to view this content.

The reason I want it to be this way is speed. Right now my worksheet has over 30,000 SUMPRODRUCT formulas. They all reference full range, which is $2:$8500 in worksheet Input. I know for each formula, the range only needs to be about 40 to 50 rows. But these row numbers will change during updates and I need the formulas to react to these changes.

I hope this makes sense, If not, I will create a sample worksheet and post it.

Thanks as usual for excellent help recieved in the past.
modytrane.

[daddylonglegs]

Better to use INDEX, i.e. instead of Input!$F$34:$F$96 try

INDEX($F:$F,N15):INDEX($F:$F,O15)

[rwgrietveld]

An alternative is to make a named range
e.g. MyRng =OFFSET(F34,0,0,counta(F34:F65536),1)

(--(F15=MyRng*

The other names can be an offset of MyRng
e.g. =offset(MyRng,12,1)

etc.

[modytrane]

Thank you both for replies.

Dll,
The "Input" in my formula refers to another worksheet named "Input".
You suggested:

Please Login or Register  to view this content.

So, how does your suggestion work without the reference to "Input" worksheet?

rwg,
I don't quite follow your suggestion. The referenced cell "F34" is on the other worksheet "Input".


In both of your suggestions, I need to keep part of my formula fixed. i.e. Input!$F$ part is fixed for all 8000+ rows. Individual formulas in each row will pick up the row reference from cells in columns N and O.

So, the formula [on worksheet Report] in cell G15 needs to add cells G$34:G$96 from worksheet "Input", whenever cell F15 [on worksheet Report] matches with cells F34:F96 on worksheet "Input".

If this doesn't make it clear, I'll post a sample file later.

Thanks,
modytrane.

[rwgrietveld]

We could try to explain. better for you to upload a small part of a WB. We'll implement the solution and you'll be happen.

[DonkeyOte]

The reason I want it to be this way is speed. Right now my worksheet has over 30,000 SUMPRODRUCT formulas. They all reference full range, which is $2:$8500 in worksheet Input. I know for each formula, the range only needs to be about 40 to 50 rows. But these row numbers will change during updates and I need the formulas to react to these changes.

INDIRECT is Volatile ... if speed is the primary concern this is not an approach you want to pursue.

If the sheet is constant (ie always Input) then you can use INDEX as advised and simply adjust the ranges to be based upon Input Sheet, ie:

Please Login or Register  to view this content.

More important however is the fact that 30000 non-volatile SUMPRODUCTs is still very bad news for performance... you should look to pursue alternatives...

in this case given you have one condition and do not need to alter the inputs as part of the calculation process you should just be using SUMIF which is significantly more efficient:

Please Login or Register  to view this content.

[modytrane]

DO,
Thanks for your suggestion.
I like the SUMIF approach.
I'll try it and report back.

Dll,
I now understand your suggestion.
Thanks,

modytrane.

[modytrane]

DO,
SUMIF seemed like it was working, but few hundred out of 8500 calculations were wrong.
So, I changed it to an Array formula:

Please Login or Register  to view this content.

Now everything works fine and speed is quite acceptable. All macros, reports and calulations are completed within 40 seconds.

Thanks for your help.

modytrane