Indirect function help please

[Guy]

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&":$AA$"&Admin!$F$15),INDIRECT("'[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.

 

[Toppers]

General format

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

A1="Fred.xls"
A2=15

HTH

 

[Guy]

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

General format

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

A1="Fred.xls"
A2=15

HTH

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&":$AA$"&Admin!$F$15),INDIRECT("'[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.

 

[Pete_UK]

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

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$"&Admi­n!$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("'["&A­dmin!$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

General format

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

A1="Fred.xls"
A2=15

HTH

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&":$AA$­"&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 -

 

[Toppers]

The file has to be open for INDIRECT to work. Have you tried this?

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

General format

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

A1="Fred.xls"
A2=15

HTH

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&":$AA$"&Admin!$F$15),INDIRECT("'[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.

 

[Guy]

..
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$19&"'!$AA$"&Admin!$E$15&":$AA$"&Admin!$F$15)<>"")/COUNTIF(INDIRECT(Admin!$A$19&"'!$AA$"&Admin!$E$15&":$AA$"&Admin!$F$15),INDIRECT(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
..

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

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

General format

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

A1="Fred.xls"
A2=15

HTH

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&":$AA$-"&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 -

 

[Guy]

..
Yes, the file is open.
..

The file has to be open for INDIRECT to work. Have you tried this?

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

General format

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

A1="Fred.xls"
A2=15

HTH

:

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&":$AA$"&Admin!$F$15),INDIRECT("'[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.

 

[Pete_UK]

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(Admin!$A$19&"'!$AA
$"&indirect("Admin!$E$"&$C$2)&":$AA$"&indirect("Admin!$F$"&$C$2))<>"")/
CO­UNTIF(INDIRECT(Admin!$A$19&"'!$AA$"&indirect("Admin!$E$"&$C$2)&":$AA
$"&indirect("Admin!$F$"&$C$2)),INDIRE­CT(Admin!$A$19&"'!$AA
$"&indirect("Admin!$E$"&$C$2)&":$AA$"&indirect("Admin!$F$"&$C$2))&""))

You will need to put 15 in cell C2.

Hope this helps.

Pete

.
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$19&"'!$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 followingto
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
.

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

Toppers, many thanks for your swift response, but I can't get that towork -
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 actuallyneed to
nest indirect statements - and that's where my brain explodes!
Thanks
Guy
:
General format
=INDIRECT("[" & A1 & "]" &"Admin!$E$" & A2))
A1="Fred.xls"
A2=15
HTH
:
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 referncea 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 -

 

[Harlan Grove]

I have the following formula that works but I want to be able to
change some more of the direct references into indirect, . . .
. . . 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&":$AA$"&Admin!$F$15),
INDIRECT("'[Fred.xls]P1-P2-P3'!$AA$"
&Admin!$E$15&":$AA$"&Admin!$F$15)&""))

Try it in stages. If the filename would be in cell Admin!X99, first
try

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

If that works, then if the 15s you mean are the row portions of the
references Admin!$E$15 and Admin!$F$15, then if the 15 were in Admin!
Y99, try

=SUMPRODUCT((L$2=INDIRECT("'["& Admin!$X$99 &"]P1-P2-P3'!$A$"&INDEX(
Admin!$E:$E,Admin!$Y$99)&":$A$"&INDEX(Admin!$F:$F,Admin!$Y$99)))
*(""<>INDIRECT("'["& Admin!$X$99 &"]P1-P2-P3'!$AA$"&INDEX(
Admin!$E:$E,Admin!$Y$99)&":$AA$"&INDEX(Admin!$F:$F,Admin!$Y$99)))
/COUNTIF(INDIRECT("'["& Admin!$X$99 &"]P1-P2-P3'!$AA$"&INDEX(
Admin!$E:$E,Admin!$Y$99)&":$AA$"&INDEX(Admin!$F:$F,Admin!$Y$99)),
INDIRECT("'["& Admin!$X$99 &"]P1-P2-P3'!$AA$"&INDEX(
Admin!$E:$E,Admin!$Y$99)&":$AA$"&INDEX(Admin!$F:$F,Admin!$Y$99))&""))

 

[Guy]

Pete

Thank you very much, that works a treat!

I was trying to separate the nested indirect statements and I suspect the
number of brackets I was entering was giving Excel a headache.......!

Cheers

Guy

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(Admin!$A$19&"'!$AA
$"&indirect("Admin!$E$"&$C$2)&":$AA$"&indirect("Admin!$F$"&$C$2))<>"")/
COUNTIF(INDIRECT(Admin!$A$19&"'!$AA$"&indirect("Admin!$E$"&$C$2)&":$AA
$"&indirect("Admin!$F$"&$C$2)),INDIRECT(Admin!$A$19&"'!$AA
$"&indirect("Admin!$E$"&$C$2)&":$AA$"&indirect("Admin!$F$"&$C$2))&""))

You will need to put 15 in cell C2.

Hope this helps.

Pete

.
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$19&"'!$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
.

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

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

:
General format

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

A1="Fred.xls"
A2=15

HTH

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 -

 

[Guy]

Harlan

Thank you for your formula. For completeness, I've adjusted the references
and got it to work as:

=SUMPRODUCT((L$2=INDIRECT(Admin!$A$19&"'!$A$"&INDEX(Admin!$E:$E,$C$2)&":$A$"&INDEX(Admin!$F:$F,$C$2)))*(""<>INDIRECT(Admin!$A$19&"'!$AA$"&INDEX(Admin!$E:$E,$C$2)&":$AA$"&INDEX(Admin!$F:$F,$C$2)))/COUNTIF(INDIRECT(Admin!$A$19&"'!$AA$"&INDEX(Admin!$E:$E,$C$2)&":$AA$"&INDEX(Admin!$F:$F,$C$2)),INDIRECT(Admin!$A$19&"'!$AA$"&INDEX(Admin!$E:$E,$C$2)&":$AA$"&INDEX(Admin!$F:$F,$C$2))&""))

I've not used the 'index' function before, so it is interesting to see how
you can have two formula (Pete_UK's and yours) come up with the same answer.

Thanks

Guy

I have the following formula that works but I want to be able to
change some more of the direct references into indirect, . . .
. . . 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&":$AA$"&Admin!$F$15),
INDIRECT("'[Fred.xls]P1-P2-P3'!$AA$"
&Admin!$E$15&":$AA$"&Admin!$F$15)&""))

Try it in stages. If the filename would be in cell Admin!X99, first
try

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

If that works, then if the 15s you mean are the row portions of the
references Admin!$E$15 and Admin!$F$15, then if the 15 were in Admin!
Y99, try

=SUMPRODUCT((L$2=INDIRECT("'["& Admin!$X$99 &"]P1-P2-P3'!$A$"&INDEX(
Admin!$E:$E,Admin!$Y$99)&":$A$"&INDEX(Admin!$F:$F,Admin!$Y$99)))
*(""<>INDIRECT("'["& Admin!$X$99 &"]P1-P2-P3'!$AA$"&INDEX(
Admin!$E:$E,Admin!$Y$99)&":$AA$"&INDEX(Admin!$F:$F,Admin!$Y$99)))
/COUNTIF(INDIRECT("'["& Admin!$X$99 &"]P1-P2-P3'!$AA$"&INDEX(
Admin!$E:$E,Admin!$Y$99)&":$AA$"&INDEX(Admin!$F:$F,Admin!$Y$99)),
INDIRECT("'["& Admin!$X$99 &"]P1-P2-P3'!$AA$"&INDEX(
Admin!$E:$E,Admin!$Y$99)&":$AA$"&INDEX(Admin!$F:$F,Admin!$Y$99))&""))