Worksheet Function Keeps Changing, need help

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jay - 18 Mar 2008 14:57 GMT

Hello all,

I've created a worksheet function that counts based on various conditions.
I got some help on these forums in creating it, and now I hope someone can
help solve a new problem.

The formula is: =SUMPRODUCT(--('Grindex
2008'!AB1:AB65535="Jan-2"),--('Grindex
2008'!Y1:Y65535="Jan-V"))+SUMPRODUCT(--('Grindex
2008'!AB1:AB65535="Jan-3"),--('Grindex 2008'!Y1:Y65535="Jan-V"))

Another individual is using the workbook, and the worksheet periodically
changes the function. It removes all the row numbers from my formula. And I
can't understand why it's happening.

The formula becomes this: =SUMPRODUCT(--('Grindex
2008'!AB:AB="Jan-2"),--('Grindex 2008'!Y:Y="Jan-V"))+SUMPRODUCT(--('Grindex
2008'!AB:AB="Jan-3"),--('Grindex 2008'!Y:Y="Jan-V"))

I've added the following Add-Ins: Conditional Sum Wizard, Solver Add-In.

I'm at a complete loss to explain the change. I set it back to the old
formula, then save and open it; it runs fine. If I leave it for a little
while (a few days to a week), then reopen it, it alters the formulas. I'm
hoping someone can see something I've done wrong, or a setting I need to
change. Any thoughts are appreciated.

Thanks,
Jay

Signature

Disregard, this is so I can find my post later.
***postedbyJay***

Reply to this Message

JP - 18 Mar 2008 15:09 GMT

If I insert a row above the formula, I can duplicate your problem.
Maybe the other user is inserting rows?

HTH-
JP

> Hello all,
>
[quoted text clipped - 28 lines]
> Disregard, this is so I can find my post later.
> ***postedbyJay***

Reply to this Message

PCLIVE - 18 Mar 2008 15:11 GMT

Possibly someone has inserted a row which then adjusts the formula's ending
row to 65536 (the last row). This causes Excel to change the reference to a
full column reference (A:A). As you have found out, the SUMPRODUCT function
will not work with full column references. While I would recommend greatly
reducing your row number reference, you might just try adding a "$", before
the 65535. It will still change to 65536 when a row is inserted, but it
doesn't appear to go any higher if another row is inserted. And once it is
at 65536, it does not appear to change when a row is deleted. I haven't
tested this to see if it works with the SUMPRODUCT function.

Good luck,
Paul

> Hello all,
>
[quoted text clipped - 27 lines]
> Thanks,
> Jay

Reply to this Message