Named ranges are causing a #REF error

[Arlen]

Hey, Everyone!

I have a table for chart data that changes two aspects depending on user
input: the sheet it pulls data from and the range on that sheet it pulls
data from.

In the main sheet SideBySide, the user chooses a city in $B$2 (sheet name)
and a product in $D$2 (range name). This formula works for the sheet
switching only...

=IF(A4>0,SUMPRODUCT(--(INDIRECT("'"&SideBySide!$B$2&"'!$B$4:$B$14200")=A4),INDIRECT("'"&SideBySide!$B$2&"'!$C$4:$C$14200"),INDIRECT("'"&SideBySide!$B$2&"'!$G$4:$G$14200")),"")

The range that needs to change is in the second INDIRECT ($C$4:$C$14200
corresponds to choosing the product Hypo.)
However, I also have products HCL (found in $D$4:$D$14200)
and Caustic ($E$4:$E$14200)

I replaced the second INDIRECT's static range with SideBySide!$D$2, hoping
to get Tacoma!Caustic for instance, but I get a #REF error.

,INDIRECT("'"&SideBySide!$B$2&"'!SideBySide!$D$2"),

Is this a matter of keeping the quotes and apostrophes straight, or is
having the same named ranges on 5 different pages a problem? I'm sure this
is very doable.

Any help would be greatly appreciated.

Thank you for your time. Have a great day!

Arlen

 

[Jim Thomlinson]

Looks like quotes, and exclamations marks is the problem...

INDIRECT("'" & SideBySide!$B$2 & "'!" & SideBySide!$D$2),

 

[Arlen]

Jim,

I haven't tried this out on my actual file, but I make a quick sample and
everything is grand!

Thank you, sir!

Arlen

 

[Arlen]

Jim,

If you have time...why do you need the single quotes around the first range
and not the second? When I was using $C$4:$C$14200, I used quotes in front
of that.

If you have time.

Thanks again.

Arlen