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!

 

[xld]

By not specifying absolute ranges, it sets it relative to the active cell.

Try

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

 

[Aladin Akyurek]

What kind of data do you have in column D of Raw Data - Text or Number?

 

[Bonz]

Text

 

[Bonz]

Gentlemen,

Thanks for taking the time to replay.
Xld your suggestion seems to have solved the issue. All of my other formulas that depended on those ranges have correctly updated. As always the help is much appreciated!

 

[Aladin Akyurek]

Text

Define Size as referring to:

=MATCH(REPT("z",255),'Raw Data'!$D:$D)-MIN(ROW('Raw Data'!$D$2))+1

Define Data or a name more appropriate for the data in column D as referring to:

=OFFSET('Raw Data'!$D$2,0,0,Size)

This corrects the issue you have and defines the data size correctly. Moreover, it allows for blanks in the range of interest.

 

[Bonz]

Aladin,

Thank you. Your help is always educational!

 

[Bonz]

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?

 

[xld]

It will ignore any non-text rows after the last text row.

 

[Bonz]

Xld,

Thanks for the clarification.