Reference a sheet name in a formula

[JRF]

I have the following formula that works just fine.

=SUMPRODUCT((Mon!$J$2:$J$65535 =
$C28)*(Mon!$L$2:$L$65535)*(Mon!$G$2:$G$65535 <> O$26))

I want to finesse the formula a little by making the sheet reference a
cell reference so the formula would look something like this:

=SUMPRODUCT((N23 & '!$J$2:$J$65535" = $C28) * (Mon!$L$2:$L$65535) *
(Mon!$G$2:$G$65535 <> O$26))

In other words I'd like the reference to the sheet name to come from
cell N23. Do any of you know who to do this?

 

[Max]

=SUMPRODUCT((Mon!$J$2:$J$65535 =

$C28)*(Mon!$L$2:$L$65535)*(Mon!$G$2:$G$65535 <> O$26))
I'd like the reference to the sheet name to come from cell N23

Think we could use INDIRECT to do this sort of thing
With the sheetname in N23, try:
=SUMPRODUCT((INDIRECT("'"&N23&"'!J2:J65535")=$C28)*(INDIRECT("'"&N23&"'!L2:l65535")*(INDIRECT("'"&N23&"'!G2:G65535")<>O$26)))

Note that the sheetname input in N23 needs to match exactly (except for
case) with the actual name on the sheet tab. Do watch out for
inconsistencies caused by typos, extra white spaces in the sheetnames,
etc.

 

[JRF]

Think we could use INDIRECT to do this sort of thing
With the sheetname in N23, try:
=SUMPRODUCT((INDIRECT("'"&N23&"'!J2:J65535")=$C28)*(INDIRECT("'"&N23&"'!L2:l65535")*(INDIRECT("'"&N23&"'!G2:G65535")<>O$26)))

Note that the sheetname input in N23 needs to match exactly (except for
case) with the actual name on the sheet tab. Do watch out for
inconsistencies caused by typos, extra white spaces in the sheetnames,
etc.

This worked great thanks! I did try using Indirect but I could not get
the syntax right.

 

[Max]

This worked great thanks! I did try using Indirect
but I could not get the syntax right.

You're welcome !
Thanks for the feedback ..