SigmaCrisis
New Member
- Joined
- Jun 7, 2017
- Messages
- 2
Hello fellow Excel aficionados!
I have a question regarding some odd behavior that one of my models is exhibiting whenever I'm making tweaks/updates to its structure. To be clear, this is not an issue with my formulas, but rather behavior when I make changes/refresh things in the formula structure -- all of my formulas are WORKING except for this odd bug which I have to work around. My hope is that maybe someone can shed some light as to why this is happening, because I'm left scratching my head, with only a vague suspicion that the answer is involved with how excel tries to compute which cells need to be calculated/recalculated at any given time.
I have the following formula that does its job splendidly:
I can copy -> paste special formula across columns, no problem. However, whenever I paste DOWN rows, the formula evaluates to #N/A. Now before you go through the effort of trying to break down the formula, please note that I've highlighted (above in red) the only cell reference that changes when you paste this formula down a row.
As expected when I paste down, the cell reference $A11 becomes $A12. That's the only change in the formula.
Observed behavior: the formula evaluates to #N/A, even when the contents of $A11 and $A12 are identical. It should actually evaluate to a number, based on the sumproduct aggregation I've set up.
Workarounds: I actually have two workaround methodologies that I've stumbled upon.
Method 1: Copy the formula text from the source formula cell, edit the target cell one row below (F2), paste the formula text, then manually change $A11 to $A12.
I don't really understand WHY this works. If I prepare a version of the formula with $A12 in a .txt file and use that to copy/paste directly into the target cell, it still evaluates to #N/A. I HAVE to paste the $A11 version and then change the reference. I can change the reference while editing the cell before pressing enter and this methodology still works.
This leads me to believe that the issue lies in how excel is determining when to recalculate/evaluate cells, and I can only 'trick' it into calculating the reference change to $A12 when I start at a known reference ($A11). Note that this behavior occurs whether I have calculations set to automatic or manual.
Method 2: Occasionally, I can get the formula pasted down that bugs to #N/A to recalculate properly if I go to the source data sheet 'Resource Pool' and delete rows BELOW the actual data. I'm talking rows 341 and below (nothing references below row 340).
The rows are empty and generally unformatted beyond the default formatting applied. I've messed around with the "used range" attribute of the sheet and am convinced it has no impact on the issue, regardless of whether or not I reset it using VBA. This further reinforces my thought that it has SOMETHING to do with tricking excel into properly re-evaluating the new formula.
----
Any thoughts/ideas on how to further pinpoint and possibly fix this issue? Bonus question: why does this behavior occur only when pasting down rows and not across columns?
A few notes that might be relevant:
-I've confirmed there's nothing weird going on with names, cell formatting, conditional formatting, data sources, etc.
-Although the system I'm using is a bit dated, I'm not running out of ram or hanging on processing.
-I don't have any macros/permanent VBA code in this file, so nothing complex should be occurring on that front.
Thanks in advance!
I have a question regarding some odd behavior that one of my models is exhibiting whenever I'm making tweaks/updates to its structure. To be clear, this is not an issue with my formulas, but rather behavior when I make changes/refresh things in the formula structure -- all of my formulas are WORKING except for this odd bug which I have to work around. My hope is that maybe someone can shed some light as to why this is happening, because I'm left scratching my head, with only a vague suspicion that the answer is involved with how excel tries to compute which cells need to be calculated/recalculated at any given time.
I have the following formula that does its job splendidly:
Code:
=SUMPRODUCT(('Resource Planning'!$F$37:$DG$37=TRIM(F$6))*('Resource Planning'!$D$39:$D$340=[COLOR=#FF0000]$A11[/COLOR])*('Resource Planning'!$B$39:$B340=$A$10)*('Resource Planning'!$F$39:$DG$340))/F$7I can copy -> paste special formula across columns, no problem. However, whenever I paste DOWN rows, the formula evaluates to #N/A. Now before you go through the effort of trying to break down the formula, please note that I've highlighted (above in red) the only cell reference that changes when you paste this formula down a row.
As expected when I paste down, the cell reference $A11 becomes $A12. That's the only change in the formula.
Observed behavior: the formula evaluates to #N/A, even when the contents of $A11 and $A12 are identical. It should actually evaluate to a number, based on the sumproduct aggregation I've set up.
Workarounds: I actually have two workaround methodologies that I've stumbled upon.
Method 1: Copy the formula text from the source formula cell, edit the target cell one row below (F2), paste the formula text, then manually change $A11 to $A12.
I don't really understand WHY this works. If I prepare a version of the formula with $A12 in a .txt file and use that to copy/paste directly into the target cell, it still evaluates to #N/A. I HAVE to paste the $A11 version and then change the reference. I can change the reference while editing the cell before pressing enter and this methodology still works.
This leads me to believe that the issue lies in how excel is determining when to recalculate/evaluate cells, and I can only 'trick' it into calculating the reference change to $A12 when I start at a known reference ($A11). Note that this behavior occurs whether I have calculations set to automatic or manual.
Method 2: Occasionally, I can get the formula pasted down that bugs to #N/A to recalculate properly if I go to the source data sheet 'Resource Pool' and delete rows BELOW the actual data. I'm talking rows 341 and below (nothing references below row 340).
The rows are empty and generally unformatted beyond the default formatting applied. I've messed around with the "used range" attribute of the sheet and am convinced it has no impact on the issue, regardless of whether or not I reset it using VBA. This further reinforces my thought that it has SOMETHING to do with tricking excel into properly re-evaluating the new formula.
----
Any thoughts/ideas on how to further pinpoint and possibly fix this issue? Bonus question: why does this behavior occur only when pasting down rows and not across columns?
A few notes that might be relevant:
-I've confirmed there's nothing weird going on with names, cell formatting, conditional formatting, data sources, etc.
-Although the system I'm using is a bit dated, I'm not running out of ram or hanging on processing.
-I don't have any macros/permanent VBA code in this file, so nothing complex should be occurring on that front.
Thanks in advance!
