Named range works in one reference, fails in another

JenniferMurphy

Well-known Member
Joined
Jul 23, 2011
Messages
2,491
Office Version
  1. 365
Platform
  1. Windows
I am stumped. I have uploaded a workbook to this Dropbox folder:


I have defined several "dynamic" named ranges. For example, Column D contains the Biden votes for each state in the 2020 election. Row 7 has been assigned the name "Header" and row 64 has been assigned the name 'Footer". This allows me to define a named range (VotesBiden) as

VBA Code:
=OFFSET(@'Final Tallies'!Header,1,0):OFFSET(@'Final Tallies'!Footer,-1,0)

This works perfectly in D4 (=SUM(VotesBiden)) but fails in K8, L8, and E8.

What am I doing wrong?

I have used dynamic ranges like this many, many times and they always work.

Thanks
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Your dynamic named ranges for VotesBiden, VotesTrump, and VotesOther are using Header and Footer named ranges that are not a static cell. The offset formula uses the top left cell in a range for the reference. I changed your dynamic named range for the those three to:

=OFFSET('Final Tallies'!$D$7,1,0,MATCH(1E+300,'Final Tallies'!$D$7:$D100003)-ROW('Final Tallies'!$D$7),1)
=OFFSET('Final Tallies'!$G$7,1,0,MATCH(1E+300,'Final Tallies'!$G$7:$G100003)-ROW('Final Tallies'!$G$7),1)
=OFFSET('Final Tallies'!$I$7,1,0,MATCH(1E+300,'Final Tallies'!$I$7:$I100003)-ROW('Final Tallies'!$I$7),1)

The formula in cell K8 works for me now.
 
Upvote 0
Solution
Your dynamic named ranges for VotesBiden, VotesTrump, and VotesOther are using Header and Footer named ranges that are not a static cell. The offset formula uses the top left cell in a range for the reference. I changed your dynamic named range for the those three to:

=OFFSET('Final Tallies'!$D$7,1,0,MATCH(1E+300,'Final Tallies'!$D$7:$D100003)-ROW('Final Tallies'!$D$7),1)
=OFFSET('Final Tallies'!$G$7,1,0,MATCH(1E+300,'Final Tallies'!$G$7:$G100003)-ROW('Final Tallies'!$G$7),1)
=OFFSET('Final Tallies'!$I$7,1,0,MATCH(1E+300,'Final Tallies'!$I$7:$I100003)-ROW('Final Tallies'!$I$7),1)

The formula in cell K8 works for me now.
I realize that Header and Footer are not static, nor are they a single cell. They are each an entire row. That's the whole point. If I add a row in the table body or reorder those rows, the table body ranges stay the same because of the offset function.

Why does the formula in D8 (=SUM(VotesBiden)) work? It uses that same dynamic range.

The curious thing is that it was working. Then I added the totals row (row 4) and it stopped working.
 
Upvote 0
As I was saying before, if you provide the OFFSET function a range instead of a single cell for the reference, it will use the top left cell in the range. For example, in this example I'm trying to show the address of the cell in the Offset function. I provided your header range and told it to look on row down, zero columns to the right, one row in height, and one column wide. It returns $A$8.
=CELL("address",OFFSET(Header,1,0,1,1))

I think what is happening in your dynamic named range formula for VotesBiden is that you're using the @ in front of the Header. That only works if the formula using the named range is in the same column as the values you want to reference. Your total for VotesBiden in cell D4 is on the same column with the Biden values. As soon as the formula tries to use that named range and it's not in the same column, it fails. As you know, the @ is used to filter the values at the intersection of the formula.

I think I would stay away from using the @ in dynamic named ranges and use them only in cell formulas.

Does that help?
 
Upvote 0
I tried removing the "@" signs. The VotesBiden range is now defined as "=OFFSET('Final Tallies'!Header,1,0):OFFSET('Final Tallies'!Footer,-1,0)". That made no difference. D4 still works, but L8 still doesn't.

I don't understand your comment about the top left cell. D4, G4, I4, & K4 all work. They all use the same dynamic range formulas.
 
Upvote 0
@JenniferMurphy
I don't understand
in D4 =SUM(VotesBiden)
in L8 =@VotesBiden
those are different formulas.

If you use in L8 =SUM(VotesBiden) or =@VotesBidenTotal then you'll get the same result.
 
Upvote 0
The formula @VotesBiden in cell L8 will give her the value in cell D8; the intersection of L8 and the VotesBiden Range; Row 8 and column D.

D4, she wants the total of all the Biden Votes.
 
Upvote 0
The formula @VotesBiden in cell L8 will give her the value in cell D8; the intersection of L8 and the VotesBiden Range; Row 8 and column D.

D4, she wants the total of all the Biden Votes.

Ah, thank you for the explanation.(y)
 
Upvote 0
Your dynamic named ranges for VotesBiden, VotesTrump, and VotesOther are using Header and Footer named ranges that are not a static cell. The offset formula uses the top left cell in a range for the reference. I changed your dynamic named range for the those three to:
After a lot of fiddling around, I finally understand what you were trying to tell me. :confused:

My resistance to your explanation was because I was positive that I had done this many times before and it worked. So I looked up one of those workbooks. Guess what? Apples and oranges.

In that workbook, I did not define an entire header and footer row. I defined a header and footer cell for each column. That gave me, as you say, a static cell, to base the Offset formula on. (y)

I have added a second sheet to the workbook on Dropbox:

Dropbox - 20210111 Election results - Simplify your life

Here's a screen shot:

1610526487820.png


The named ranges for Product A are at the bottom. Now both the monthly and product totals work and are correct. There are similar named ranges for Products B and C.

I did not try your formulas because they are too complicated for my addled brain to ever remember. But trusting that they do, I'll mark that post as the solution.

Thanks for the patience...

-j
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

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