Think you are just missing an apostrophe (')
within the front double quotes part of the INDIRECT:
.... --(INDIRECT(" ' " &X2&"'!$A$3:$A$50149")='NU Budget'!$A$1)
shown spaced out above for clarity: " ' "

Try inserting the apostrophe in all the affected terms,
and it should work ok
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:365 Subscribers:65
xdemechanik
---
"jbo" wrote:
I have a workbook with numerous worksheets that are summarized on one
worksheet. Each worksheet has a name which is also on the summary worksheet.
I am trying to set up an indirect formula within a sumproduct formula that
will allow me to pull data from a worksheet with a name that matches the name
on my summary worksheet. For example, cell X2 on worksheet NU Summary has
the text "Booth" which matches the name of one of the supporting worksheets.
My current formula, which works, looks like this:

=SUMPRODUCT(--(Booth!$B$3:$B$50149='NU
Budget'!$A$1),--(Booth!$B$3:$B$50149=$A5),--(Booth!$C$3:$C$50149='NU
Budget'!$B5),--(Booth!$D$3:$D$50149='NU
Budget'!$C5),--(Booth!$E$3:$E$50149='NU Budget'!$D5),(Booth!$F$3:$F$50149))

I am trying to make Booth an indirect reference but am having difficulty.
Based on other discussions on this board, I came up with the following
formula:

=SUMPRODUCT(--(INDIRECT(""&X2&"'!$A$3:$A$50149")='NU
Budget'!$A$1),--(INDIRECT(""&X2&"'!$B$3:$B$50149")=$A4),--(INDIRECT(""&X2&"'!$C$3:$C$50149")='NU
Budget'!$B4),--(INDIRECT(""&X2&"'!$D$3:$D$50149")='NU
Budget'!$C4),--(INDIRECT(""&X2&"'!$E$3:$E$50149")='NU
Budget'!$D4),(INDIRECT(""&X2&"'!$F$3:$F$50149")))

However, I am getting a "REF!" error message in the cell. Any suggestions
on how I can fix this?