sumif(indirect >issue (syntax)

[bluepenink]

hello all

having some trouble placeing the "" or syntax portion...i keep getting the error "too many arguments"....

here is my formula

=SUMIF(INDIRECT("'"&$C$3&"'!"&$A$2:$A$54,F$1,INDEX(INDIRECT("'"&$C$3&"'!"&$C$2:$X$54,0,MATCH($C48,INDIRECT("'"&$C$3&"'!"&$C$1:$X$1,0))))))

basically, $C$3 = person's name aka worksheet name, so i have a drop down menu, so when C3 changes, i want the name to look off the worksheet


here is my original formula, prior to placing the indirect
=SUMIF('Milwaukee (SE&ISE) Data'!$A$2:$A$54,'Milwaukee (2)'!F$1,INDEX('Milwaukee (SE&ISE) Data'!$C$2:$X$54,0,MATCH($C48,'Milwaukee (SE&ISE) Data'!$C$1:$X$1,0)))

im trying to change that

'Milwaukee (SE&ISE) Data'! .........with $C$3

can someone pls help!

thx u

 

[Domenic]

Try...

=SUMIF(INDIRECT("'"&$C$3&"'!A2:A54"),INDIRECT("'"&$C$3&"'!F1"),INDEX(INDIRECT("'"&$C$3&"'!C2:X54"),0,MATCH(C48,INDIRECT("'"&$C$3&"'!C1:X1"),0)))

 

[bluepenink]

hello F$1 is in the same workbook...(and my excel is busy calculating) so before i try to code...is it ok if i use it in the following method

=SUMIF(INDIRECT("'"&$C$3&"'!A2:A54"),F$1,INDEX(INDIRECT("'"&$C$3&"'!C2:X54"),0,MATCH(C48,INDIRECT("'"&$C$3&"'!C1:X1"),0)))

i changed it to F$1

thx u so much.

 

[Aladin Akyurek]

hello all

having some trouble placeing the "" or syntax portion...i keep getting the error "too many arguments"....

here is my formula

=SUMIF(INDIRECT("'"&$C$3&"'!"&$A$2:$A$54,F$1,INDEX(INDIRECT("'"&$C$3&"'!"&$C$2:$X$54,0,MATCH($C48,INDIRECT("'"&$C$3&"'!"&$C$1:$X$1,0))))))

basically, $C$3 = person's name aka worksheet name, so i have a drop down menu, so when C3 changes, i want the name to look off the worksheet


here is my original formula, prior to placing the indirect
=SUMIF('Milwaukee (SE&ISE) Data'!$A$2:$A$54,'Milwaukee (2)'!F$1,INDEX('Milwaukee (SE&ISE) Data'!$C$2:$X$54,0,MATCH($C48,'Milwaukee (SE&ISE) Data'!$C$1:$X$1,0)))

im trying to change that

'Milwaukee (SE&ISE) Data'! .........with $C$3

can someone pls help!

thx u

It sshould be:

=SUMIF(INDIRECT("'"&$C$3&"'!$A$2:$A$54"),F$1,
    INDEX(INDIRECT("'"&$C$3&"'!$C$2:$X$54"),0,
      MATCH($C48,INDIRECT("'"&$C$3&"'!$C$1:$X$1"),0)))

 

[bluepenink]

Hello

thx u for your help.

for some odd reason, it;s giving me a #N/A error?

i cant seem to understanding why. using my original formula, for milwaukee, it's now looking for a sheet called i.e. "John Doe" in C3...but returning a #N/A. the formatting and layout is the exact same as the milwaukee sheet?

any ideas? thx u guys!

 

[Domenic]

First, just in case you're using the formula I offered, the column reference for cell C48 should have been an absolute reference. Therefore, C48 should have been $C48.

If you're still getting an error, what values do C48 and C1:X1 contain?

 

[bluepenink]

hello guys
thx u so much...it was suppose to change it o B1-X1 instead of C1, thx u so much once again, superb help!!

u guys are the best!!!! cheers

 

[Aladin Akyurek]

Hello

thx u for your help.

for some odd reason, it;s giving me a #N/A error?

i cant seem to understanding why. using my original formula, for milwaukee, it's now looking for a sheet called i.e. "John Doe" in C3...but returning a #N/A. the formatting and layout is the exact same as the milwaukee sheet?

any ideas? thx u guys!

If C3 houses John Doe and there is a sheet of that name, the formula should work. By the way, I took C3, C48, and F1 to be cells in the sheet from within the formula is invoked.