|
Indirect function help please
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. |
Indirect function help please
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. |
Indirect function help please
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. |
Indirect function help please
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$"&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 "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)*(IN DIRECT("'[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),INDIRE CT("'[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 - |
Indirect function help please
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. |
Indirect function help please
..
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 .. "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$"&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)*(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 - |
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. |
Indirect function help please
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 - |
Indirect function help please
Guy wrote...
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))&"")) |
Indirect function help please
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 "Pete_UK" wrote: 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(Ad min!$A$19&"'!$AA $"&indirect("Admin!$E$"&$C$2)&":$AA$"&indirect("Ad min!$F$"&$C$2))<"")/ COUNTIF(INDIRECT(Admin!$A$19&"'!$AA$"&indirect("Ad min!$E$"&$C$2)&":$AA $"&indirect("Admin!$F$"&$C$2)),INDIRECT(Admin!$A$1 9&"'!$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$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 . "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 - |
Indirect function help please
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$"&INDE X(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)),INDIR ECT(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 "Harlan Grove" wrote: Guy wrote... 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))&"")) |
| All times are GMT +1. The time now is 02:01 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com