Indirect function help please
..
Yes, the file is open.
..
"Toppers" wrote:
The file has to be open for INDIRECT to work. Have you tried this?
"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$"&Admin!$E$&$C$2&":$AA$"&Admin!$F$&$ C$2)<"")/COUNTIF(INDIRECT("'[&Admin!$A$19&]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)*(IND IRECT("'[Fred.xls]P1-P2-P3'!$AA$"&Admin!$E$15&":$AA$"&Admin!$F$15)<"")/COUNTIF(INDIRECT("'[Fred.xls]P1-P2-P3'!$AA$"&Admin!$E$15&":$AA$"&Admin!$F$15),INDIREC T("'[Fred.xls]P1-P2-P3'!$AA$"&Admin!$E$15&":$AA$"&Admin!$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.
|