Sumproduct with multiple criteria using indirect formula

[jbo]

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?

 

[Max]

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

 

[Shane Devenshire]

Hi,

Max has already found your apostrophy problem, but I though I would point
out that you could simplify this formula by using range names A, B, CC, D, E,
F for the ranges on the indirect sheet, and NA, NB, NC, and ND for the
references on the Nu Budget sheet:

=SUMPRODUCT(--(INDIRECT("`"&X2&"'!$A")=NA),--(INDIRECT("`"&X2&"'!B")=$A4),--(INDIRECT("`"&X2&"'!CC")=NB),--(INDIRECT("`"&X2&"'!D")=NC),--(INDIRECT("`"&X2&"'!E")=ND),(INDIRECT("`"&X2&"'!F")))

If this helps, please click the Yes button

cheers,
Shane Devenshire

 

[jbo]

That worked great. Thanks! Can you explaint to me why that worked? I just
discovered the indirect formula yesterday and, therefore, do not really
understand how the apostrophe's and & symbols work to construct the formula.

 

[Max]

INDIRECT basically resolves/"activates" concatenated strings into desired
range references. The 2 apostrophes work as a pair (you missed the front
ones earlier). They're needed once the sheetname contains a space/s. For
sheetnames w/o any spaces, the apostrophes are not needed, but its still
fine with the apostrophes inserted. Hence its safer in general to always use
the pair of apostrophes.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:365 Subscribers:65
xdemechanik