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

M

Matty

Well-known Member
Joined
Feb 17, 2007
Messages
3,717
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
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Sort by date Sort by votes
Nevermind - now sorted. It was as simple as adding the difference to the cols part of the OFFSET!

Doh!

Matty
 
Upvote 0
Matty said:
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.
Click to expand...

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

Code: 
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.
 
Upvote 0
Hi DonkeyOte,

Thanks for responding.

I am guessing you're using the DNR to negate the need for the Array in the first instance ?
Click to expand...
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.
Click to expand...
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
 
Upvote 0
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)
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
Matty said:
doesn't an INDEX:INDEX set up also make such a formula semi-volatile?

Cheers,

Matty
Click to expand...
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.
 
Upvote 0
Matty said:
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.
Click to expand...

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)
 
Upvote 0
You must log in or register to reply here.

Similar threads

Jeffrey Mahoney
  • Question Question
Named Range Lambda Formula to Create UDF
Replies
4
Views
243
Jeffrey Mahoney
Jeffrey Mahoney
R
  • Question Question
Linked dynamic named ranges - unexpected results.
Replies
2
Views
199
Ramses505
R
M
  • Solved
Dynamically Plot Data Points in Scatterplot
Replies
5
Views
665
felixstraube
F
E
  • Question Question
VBA - If Cell IsDate and Another Column is a formula then make Cell a hyperlink
2
Replies
10
Views
163
Alex Blakenburg
A
H
  • Solved
Graph data series named range challenges - terribly frustrating
Replies
2
Views
81
hcgood
H

Forum statistics

Threads
1,224,586
Messages
6,179,729
Members
452,939
Latest member
WCrawford

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top