Try this:

Assume your drop down list is in cell A1.

SUMPRODUCT(--('IS Data'!$D$3:$D$11072=$A6)*('IS
Data'!$B$3:$B$11072=$C$3),INDIRECT(A1))

However, if your named ranges are dynamic themselves, this may not work.


--
Biff
Microsoft Excel MVP


"acyakos" wrote in message
...
I have the following formula:

SUMPRODUCT(--('IS Data'!$D$3:$D$11072=$A6)*('IS
Data'!$B$3:$B$11072=$C$3),(Act1))


Where I am summing all of the data in the named range "Act1" on the IS
Data
tab which meets the criteria that the data in column D = A6 (or 1) and the
data in column B = C3, or "Oklahoma". How do I make the reference to the
name range "Act1" dynamic; i.e. I want to be able to use a dropdown menu
or
something like that to change Act1 to Act2, Act 3, Act4, etc. and have the
formula return the correct sum. I have tried referencing a dropdown menu
with these values and I get a #VALUE error.

Any ideas?

Thanks!