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(A40,SUMPRODUCT(--(INDIRECT("'"&SideBySide!$B$2&"'!$B$4:$B$14200")=A 4),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