Have a try with this, then:

=SUMPRODUCT((INDIRECT(Admin!$A$19&"'!$A$"&indirect ("Admin!$E$"&$C$2)&":
$A$"&indirect("Admin!$F$"&$C$2))=G*$2)*(INDIRECT(A dmin!$A$19&"'!$AA
$"&indirect("Admin!$E$"&$C$2)&":$AA$"&indirect("Ad min!$F$"&$C$2))<"")/
CO*UNTIF(INDIRECT(Admin!$A$19&"'!$AA$"&indirect("A dmin!$E$"&$C$2)&":$AA
$"&indirect("Admin!$F$"&$C$2)),INDIRE*CT(Admin!$A$ 19&"'!$AA
$"&indirect("Admin!$E$"&$C$2)&":$AA$"&indirect("Ad min!$F$"&$C$2))&""))

You will need to put 15 in cell C2.

Hope this helps.

Pete

On Aug 1, 4:00 pm, Guy wrote:
.
Pete, thank you for your response. The formula is not on the 'admin' sheet.
I've amended the cell that relates to the filename so that it now also
contains the tab name and come up with the following that works:

=SUMPRODUCT((INDIRECT(Admin!$A$19&"'!$A$"&Admin!$E $15&":$A$"&Admin!$F$15)=G*$2)*(INDIRECT(Admin!$A$1 9&"'!$AA$"&Admin!$E$15&":$AA$"&Admin!$F$15)<"")/CO*UNTIF(INDIRECT(Admin!$A$19&"'!$AA$"&Admin!$E$15 &":$AA$"&Admin!$F$15),INDIRE*CT(Admin!$A$19&"'!$AA $"&Admin!$E$15&":$AA$"&Admin!$F$15)&""))

I've played around with a small part of the formula and got the following to
work that replaces the '15' with an indirect cell reference:

=INDIRECT(Admin!$A$19&"'!$A$"&(INDIRECT("Admin!$E$ "&$C$2)))

but I can't seem to be able to expand that principle to the whole
formula.......any suggestions gratefully received.

Thank you.

Guy
.



"Pete_UK" wrote:
Is the formula in a cell which is in the Admin sheet? If so, then you
don't really need to have all those sheet references in your formula
(which makes it more difficult to follow). Your original formula would
then become:

=SUMPRODUCT((INDIRECT("'[Fred.xls]P1-P2-P3'!$A$"&$E$15&":$A$"&$-F$15)=L
$2)*(INDIRECT("'[Fred.xls]P1-P2-P3'!$AA$"&$E$15&":$AA$"&$F$15)<"")/
COUNTIF(INDIRECT("'[Fred.xls]P1-P2-P3'!$AA$"&$E$15&":$AA$-"&$F
$15),INDIRECT("'[Fred.xls]P1-P2-P3'!$AA$"&$E$15&":$AA$"&$F$15)&""))

Also, if you always want to refer to the data in sheet P1-P2-P3 of the
other file (Fred.xls), then you could put:

[Fred.xls]P1-P2-P3

in A1 to also make it easier to read.

Also note that INDIRECT will only work with open files, so Fred.xls
(or whatever) would have to be open for the formula to work.

Hope this helps.

Pete

On Aug 1, 2:46 pm, Guy wrote:
Toppers, many thanks for your swift response, but I can't get that to work -
I come up with:

=SUMPRODUCT((INDIRECT("'["&Admin!$A$19&"]P1-P2-P3'!$A$"&Admin!$E$"&$C$2&":$*-A$"&Admin!$F$"&$C$2)=L$2)*(INDIRECT("'["&Admin!$A$19&"]P1-P2-P3'!$AA$"&Adm*in!$E$"&$C$2&":$AA$"&Admin!$F$"&$C$2 )<"")/COUNTIF(INDIRECT("'["&Admin!$A$1*9-&"]P1-P2-P3'!$AA$"&Admin!$E$"&$C$2&":$AA$"&Admin!$F$"&$C$2) ,INDIRECT("'["*&Admin!$A$19&"]P1-P2-P3'!$AA$"&Admin!$E$"&$C$2&":$AA$"&Admin!$F$"&$C$2) &"")*)

but I'm wondering whether because the '15' substitution I want to make is
actually to parts of the existing indirect references then I actually need to
nest indirect statements - and that's where my brain explodes!

Thanks

Guy

"Toppers" wrote:
General format

=INDIRECT("[" & A1 & "]" &"Admin!$E$" & A2))

A1="Fred.xls"
A2=15

HTH

"Guy" wrote:

Hi

I have the following formula that works but I want to be able to change some
more of the direct references into indirect, but I'm struggling to work out
the format and bracket arrangements. Specifically I want to change every
reference to 'fred.xls' to reference a cell that contains a filename as text,
and I want to change every instance of the value '15' to refernce a cell that
contains the value. I hope that makes sense.

=SUMPRODUCT((INDIRECT("'[Fred.xls]P1-P2-P3'!$A$"&Admin!$E$15&":$A$"&Admin!$*-F$15)=L$2)*(INDIRECT("'[Fred.xls]P1-P2-P3'!$AA$"&Admin!$E$15&":$AA$"&Admin*!-$F$15)<"")/COUNTIF(INDIRECT("'[Fred.xls]P1-P2-P3'!$AA$"&Admin!$E$15&":$A*A$-"&Admin!$F$15),INDIRECT("'[Fred.xls]P1-P2-P3'!$AA$"&Admin!$E$15&":$AA$"&*Adm-in!$F$15)&""))

Also, is what I am trying to do sensible, or should I be going about this in
a different way?

Thank you.

Guy.- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -