Can anyone tell me what's wrong with this formula?

=SUMPRODUCT(SUMIF(INDIRECT([File_Name.xlsm]SheetName&"'"&$A$176:$A$194&"'!j:j"),D3,INDIRECT([File_Name.xlsm]SheetName&"'"&$A$176:$A$194&"'!p:p")))

I am getting #NAME? error.

I am using Excel 2007.
I have the external file open.
I have the Analysis Toolpak Add-Ins.

I believe there must be something wrong with where I have the " ' ! but I
just can't seem to figure it out.

Any ideas would be appreciated!

Here's my best guess:

=SUMPRODUCT(SUMIF(INDIRECT("'[File_Name.xlsm]"&$A$176:$A$194&"'!J:J"),D3,INDIRECT("'[File_Name.xlsm]"&A$176:$A$194&"'!P:P")))

--
Biff
Microsoft Excel MVP


"gina1221" wrote in message
...
Can anyone tell me what's wrong with this formula?

=SUMPRODUCT(SUMIF(INDIRECT([File_Name.xlsm]SheetName&"'"&$A$176:$A$194&"'!j:j"),D3,INDIRECT([File_Name.xlsm]SheetName&"'"&$A$176:$A$194&"'!p:p")))

I am getting #NAME? error.

I am using Excel 2007.
I have the external file open.
I have the Analysis Toolpak Add-Ins.

I believe there must be something wrong with where I have the " ' ! but I
just can't seem to figure it out.

Any ideas would be appreciated!

Why don' you tell in words what you are trying to do?

--
__________________________________
HTH

Bob

"gina1221" wrote in message
...
Can anyone tell me what's wrong with this formula?

=SUMPRODUCT(SUMIF(INDIRECT([File_Name.xlsm]SheetName&"'"&$A$176:$A$194&"'!j:j"),D3,INDIRECT([File_Name.xlsm]SheetName&"'"&$A$176:$A$194&"'!p:p")))

I am getting #NAME? error.

I am using Excel 2007.
I have the external file open.
I have the Analysis Toolpak Add-Ins.

I believe there must be something wrong with where I have the " ' ! but I
just can't seem to figure it out.

Any ideas would be appreciated!

I have an Excel workbook with approximately 65 sheets. I need to sum the
values in column J where it matches column D in an external sheet and return
the value from column P for each of the sheets in the workbook. I have the
list of sheet names listed on a sheet inside the workbook and that's how I'm
trying to use INDIRECT to pull information from all of the sheets instead of
just SUMIF and listing each sheet individually as this takes an inordinate
amount of time given the number of sheets in use.

Thanks, Gina

"Bob Phillips" wrote:

Why don' you tell in words what you are trying to do?

--
__________________________________
HTH

Bob

"gina1221" wrote in message
...
Can anyone tell me what's wrong with this formula?

=SUMPRODUCT(SUMIF(INDIRECT([File_Name.xlsm]SheetName&"'"&$A$176:$A$194&"'!j:j"),D3,INDIRECT([File_Name.xlsm]SheetName&"'"&$A$176:$A$194&"'!p:p")))

I am getting #NAME? error.

I am using Excel 2007.
I have the external file open.
I have the Analysis Toolpak Add-Ins.

I believe there must be something wrong with where I have the " ' ! but I
just can't seem to figure it out.

Any ideas would be appreciated!