Please suggest a more compact format for this formula:
=SUMPRODUCT(
(INDIRECT("RawData!B"&$I$32&":B"&$J$32)=105)*
(INDIRECT("RawData!AA"&$I$32&":AA"&$J$32)="M6")*
(INDIRECT("RawData!AG"&$I$32&":AG"&$J$32)="CLOSED")*
(INDIRECT("RawData!P"&$I$32&":P"&$J$32)="CLOSED")*
(INDIRECT("RawData!AD"&$I$32&":AD"&$J$32)>=CalculatedData!H2)*
(INDIRECT("RawData!AD"&$I$32&":AD"&$J$32)<=CalculatedData!H3)
(INDIRECT("RawData!AF"&$I$32&":AF"&$J$32)>INDIRECT("RawData!AD"&$I$32&":AD"&$J$32))
((ISNUMBER(MATCH(INDIRECT("RawData!E"&$I$32&":E"&$J$32),Definitions!$B$3:$B$6,0)))))
)
Also, is there a way to alias frequent constructs, like
("RawData!B"&$I$32&":B"&$J$32) and replace with a parametrized syntax, like
“data(B)”, still using a worksheet formula.
Thank you.
Bernard Liengme - 03 Feb 2008 20:56 GMT
Last part of question:
I used Define Names to define: Somewhere --- Refers to "Sheet3!C"
In a cell on Sheet1 I use =INDIRECT(Somewhere&G1)
This returned the value from C1 in Sheet3
I could have used a shorter name but you must avoid names that could be
mistaken for cell references
best wishes

Signature
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email
> Please suggest a more compact format for this formula:
> =SUMPRODUCT(
[quoted text clipped - 14 lines]
>
> Thank you.
Herbert Seidenberg - 04 Feb 2008 02:44 GMT
Here is my suggestion, using defined names:
http://www.freefilehosting.net/download/3bddd
adimar - 11 Feb 2008 18:54 GMT
Some feedback: Having cleaned-up and re-organized my application I must tell
you the advice you gave me in this post made a huge difference in efficiency
and readability.
Please let me know if you have other “best practices” you can share.
Thank you!
> Here is my suggestion, using defined names:
> http://www.freefilehosting.net/download/3bddd