Complicated array problem

[semper_si]

I've hit a bit of snag in a logbook that I'm trying to create Excel. Essentially I have an array that I would like to act on an 3D reference, but alas Excel doesn't seem to allow this. Is there a way round this?

The formula in question is:

(SUMPRODUCT(--('Sheet1'!$G$2:$G$500=A8),--($H$3<='Sheet1'!$A$2:$A$500),--($L$3>='Sheet1'!$A$2:$A$500)))

Where it says sheet1, I'd ideally like something along the lines of 'Sheet1:Sheet10'!. I'd also want to link this to an external workbook, but I thought I'd see if anyone can help with this connundrum first!

Thanks for taking the time to read this, and thanks in advance for anyone who can shed any light on this!

Simon

[rylo]

Simon

From what I've read, there are problems trying to use SUMPRODUCT in 3D mode. There are some fancy uses of the INDIRECT function that can overcome some of the issues, but I don't think they would be effective for your situation (just my guess, and I wouldn't be surprised if someone came up with a way!!!).

The Morefunc addins have a function THREED that seems to be getting some success in conjunction with SUMPRODUCT, but I've never used them. Refer http://xcell05.free.fr/

Another option would be to build your own function specifically to cover your requirements.


rylo

[semper_si]

Thanks for the advice! I'm not sure how to build a function, though I wonder if I might get a script to work to do this - of course, that means I need to learn Visual Basic

[rylo]

Hi

Here's a start for you. Put the code below into a general module in the relevant workbook.

Assumptions.

1) This function will be called from the first sheet in the workbook.
2) It will cycle through all the other sheets in the workbook, starting from sheet2.
3) Call the function in the first sheet as
=mysumproduct(A8,H3,L3,"G2:G500")

If you only have 2 sheets in your workbook (the first sheet and one other) then this should return the same result as the sumproduct formula. If you have more than 2 sheets, it should return the 3D result.


HTH

rylo

[semper_si]

Thanks sooo much! I'll give it a try and see how it goes

[semper_si]

Hi

Here's a start for you. Put the code below into a general module in the relevant workbook.

Assumptions.

1) This function will be called from the first sheet in the workbook.
2) It will cycle through all the other sheets in the workbook, starting from sheet2.
3) Call the function in the first sheet as
=mysumproduct(A8,H3,L3,"G2:G500")

If you only have 2 sheets in your workbook (the first sheet and one other) then this should return the same result as the sumproduct formula. If you have more than 2 sheets, it should return the 3D result.


HTH

rylo

I'm sorry... which function?

[rylo]

Hi

ROFL Sorry here it is.

Please Login or Register  to view this content.

rylo