|
|
|
|
http://www.contextures.com/xlNames01.html#Dynamic
Regards,
Peo Sjoblom
"shmurphing" wrote:
OK. Just a little confused. So how does the formula know that rangname1=
column C? Or does it look at the entire sheet for the word incident? What
if that word appears in other columns -- either by itself or within a phrase?
"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?
|