If you are unfamiliar with dynamic named ranges:
http://www.cpearson.com/excel/named.htm
Scroll down a bit to find the section about Dynamic Named Ranges.

tj

"JBoulton" wrote:

I have that exact situation and solved it using dynamic range names. That
would change your formula to
=SUMPRODUCT(--(rangename1="Incident"),--(rangename2C2)). As I said, that
solved the problem for me.

HTH

"shmurphing" wrote:

So I have a sheet that gaves external data from OBDC. I am using a lot of
SUMPRODUCT formulas.

Everytime I update the query all the formulas ranges seem to change.
IE:
I have set a formula of:
=SUMPRODUCT(--('Wpg #s data'!C6:C500="Incident"),--('Wpg #s data'!F6:F500C2))

And it changes to:
=SUMPRODUCT(--('Wpg #s data'!C6:C415="Incident"),--('Wpg #s data'!F6:F415C2))

Why is that happening? I will usually have a range of around 300 but I am
concerned that if it changes it on the fly that there could be issues with
the collection of data and therefore the report could be wrong. Any thoughts?