Dynamic Named Range somehow changes!

[Bonz]

Good Morning All

Does anyone have any idea what would cause the formula for a dynamic named range to keep changing on its own? I enter the following formula:

OFFSET('Raw Data'!$D$2,0,0,COUNTA('Raw Data'!D:D),1)

Which I save and somehow gets changed to the following formula:

OFFSET('Raw Data'!$D$2,0,0,COUNTA('Raw Data'!XFD:XFD),1)

This is happening to several named ranges I created. I’ve tried to correct this by editing the formula which seems to work but changes again. I’ve also deleted the named range and recreated it with the same change process taking place.

In case it matters the named ranges are being created on a table imported into Excel via MS Query. Anyone know how to correct this? Any help would be appreciated—Thank You!

 

[Aladin Akyurek]

Aladin,

I’m curious, how would your formulas change if the columns contained: date/time, numeric literals, since there is no use of COUNT or COUNTA?

Suppose that your column D is a date column...

Only the definition of Size would slightly change:

=MATCH(9.99999999999999E+307,'Raw Data'!$D:$D)-MIN(ROW('Raw Data'!$D$2))+1

COUNT and COUNTA will flounder on empty and or blank cells. Dynamic range definitions also cover often enough ranges generated by formulas. These two functions become then useless. I'd like also to add that the MATCH bit is faster than a COUNT or COUNTA expression.

 

[Bonz]

I’m not certain this should be posted here as a continuation of the above thread or as a new one but I'm doing so just in case. Kindly let me know if a new thread is called for and I will gladly do so.

I’ve been attempting to resolve a Sumproduct issue with the following formula:
SUMPRODUCT(--(TEXT(Start_Date,"mmm-yy")=E$12),--(WorkOrderType="PM"),--(Labor))

Using Aladin’s instructions above
Range Name “Labor” in column G containing numeric values is defined as:

=OFFSET('Raw Data'!$G$2,0,0,SizeG)

where “SizeG” is:

=MATCH(9.99999999999999E+307,'Raw Data'!$G:$G)-MIN(ROW('Raw Data'!$G$2))+1

has in my case generated a #Value! Error.

The problem I suspect is that the named range “Labor” contains blanks both at the start and end of the column.
Attempting to resolve the issue I followed Aladin’s explanation as were detailed here

http://www.mrexcel.com/forum/showthread.php?t=33856&page=2

which has led me to create the following formula:

SUMPRODUCT(--(TEXT(Start_Date,"mmm-yy")=G$12),--(WorkOrderType="PM"),--(INDEX(Data,0,7)))

The instructions were clear and I think understood. I tested the following formula:

=OFFSET('Raw Data'!$A$2,0,0,Admin!$B$3,Admin!$B$4)

and saw the needed “march of ants” around my data table(imported via MS Query).
As mentioned the named range “Labor” contains numeric values and is in column 7 of my data table which consists of 8 columns in total A:H.
Unfortunately (for me), I still receive a #Value! Error. I’ve searched the board for additional insight and have found other suggestive solutions but none seem to directly apply. I’m not sure how to continue at this point. Any help would be happily welcomed.

 

[Aladin Akyurek]

Would you re-post

SUMPRODUCT(--(TEXT(Start_Date,"mmm-yy")=G$12),--(WorkOrderType="PM"),--(INDEX(Data,0,7)))

with the real ranges substituted for Start_Date, WorkOrderType and Data?

 

[Bonz]

Aladin,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I will gladly repost. For those interested the new thread may be found here:<o></o>
<o></o>
http://www.mrexcel.com/forum/showthread.php?p=1798845&posted=1#post1798845<o></o>