Non-anchored (Column) dynamic Named Range help...

[Matty]

Hi All,

I'm interested to know whether there's a way to trick Excel when working with a non-anchored (Column) dynamic Named Range.

I can use the following formula as a Named Range in Sheet1:

=OFFSET(O$2,1,0,ROWCOUNT,1)

And when I refer to it in Sheet2, the O$2 part will change depending on the Column I am in, i.e. if I move to Column B on Sheet2, the Named Range will be as such:

=OFFSET(B$2,1,0,ROWCOUNT,1)

The problem I have is that the data in Sheet2 is in a different place to Sheet1, so I am picking up the wrong values when I use this Named Range in my formulas.

So I was wondering whether it's possible to "trick" Excel into thinking you're in a Column you're not actually in?

I wouldn't normally go down this route, but I am summing a matrix of data and the array formulas that are currently doing the job are slowing things down to a snail's place.

I'm open to any suggestions people have...

Thanks,

Matty

 

[Matty]

Nevermind - now sorted. It was as simple as adding the difference to the cols part of the OFFSET!

Doh!

Matty

 

[DonkeyOte]

I am summing a matrix of data and the array formulas that are currently doing the job are slowing things down to a snail's place.

Matty, I am guessing you're using the DNR to negate the need for the Array in the first instance ?

If not you should be avoiding OFFSET (IMO) as the Arrays will become Volatile by association.

If you are you might find you can just use a SUMIF with INDEX & MATCH to establish summation range thereby avoiding need for DNR

Sheet2!B2:
=SUMIF(Sheet1!$A:$A,$A2,INDEX(Sheet1!$A:$IV,0,MATCH(B$1,Sheet1!$1:$1,0)))

Above just a rough example obviously and assumes B$1 header on Sheet2 may appear anywhere in Sheet1 (other than Col A which we assume to be fixed ... if not we could replace with another INDEX/MATCH)

I confess I generally avoid using OFFSET in DNR's (and use INDEX) unless the DNR is to be used exclusively for Data Validation.

 

[Matty]

Hi DonkeyOte,

Thanks for responding.

I am guessing you're using the DNR to negate the need for the Array in the first instance ?

I'm using the DNR because I prefer to reference ranges that correspond exactly to my data set. I've just got into this habit in order to avoid using entire Columns (which I thought could slow things down). Correct me if I'm wrong here.

If not you should be avoiding OFFSET (IMO) as the Arrays will become Volatile by association.

I understand that OFFSET is volatile, but I can't say that I've ever had much of an issue with using it. What are the negatives when using it within a DNR?

I'll must also make a confession: I had forgotten about the old INDEX and MATCH trick within SUMIF, which is something I've seen you demonstrate here before. I will have a play with it within SUMIFS to see if it works, as it is SUMIFS that I'm using in this specific scenario.

Grateful as always for your input.

Cheers,

Matty

 

[mikerickson]

A non-volatile alternative to OFFSET for a dynamic named range

=INDEX(Sheet1!$A:$A, 1, 1):INDEX(Sheet1!$A:$A, COUNTA(Sheet1!$A:$A), 1)

instead of

=OFFSET(Sheet1!$A$1, 0, 0, COUNTA(Sheet1!$A:$A), 1)

 

[Matty]

Hi Mike,

Thanks for that. I've also used a similar formula in the past as a DNR.

I just always seems to default back to OFFSET as I can type such a formula in with my eyes closed and I've never seen any negatives due to its volatility (or perhaps I'm just not appreciating the negatives).

Thinking about it though, doesn't an INDEX:INDEX set up also make such a formula semi-volatile?

Cheers,

Matty

 

[mikerickson]

The precedents of the INDEX:INDEX are all of column A. Which is appropriate because the DNR represents "all of column A (that is used)"

I've seen performance issues with volatile DNR's. Nothing critical (for those particular uses), but clearly a slowing of the sheet. OFFSET is a good quick & dirty approach.

 

[T. Valko]

doesn't an INDEX:INDEX set up also make such a formula semi-volatile?

Cheers,

Matty

It'll calculate when the file is opened and Excel will consider that a change so you will get prompted to "save changes" when you go to close the file. However, it will not recalculate afterwards unless it's dependencies change so technically it is not volatile.

I think Charles Williams has something about this on his site.

 

[DonkeyOte]

I've just got into this habit in order to avoid using entire Columns (which I thought could slow things down). Correct me if I'm wrong here.

Matty, regards the above...

My understanding from the likes of Charles W is that most (though by no means all) functions use only the Used Range Intersect of the Precedent range. Given this fact the overhead of calculating a Dynamic Range can at times prove unwarranted and arguably detrimental ... not that you would notice in theses instances of course (the functions being efficient in their own right).

I will use OFFSET occasionally if creating a DNR for use solely as source for DV List as this is a non-volatile arrangement ... the DNR is only evaluated when the DV cell is itself activated and DV invoked (at least that's my understanding)