Named range works in one reference, fails in another

JenniferMurphy

Well-known Member
Joined
Jul 23, 2011
Messages
1,438
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
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

Jeffrey Mahoney

Well-known Member
Joined
May 31, 2015
Messages
1,830
Office Version
  1. 365
Platform
  1. Windows
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.
 
Solution

JenniferMurphy

Well-known Member
Joined
Jul 23, 2011
Messages
1,438
Office Version
  1. 365
Platform
  1. Windows
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.
 

Jeffrey Mahoney

Well-known Member
Joined
May 31, 2015
Messages
1,830
Office Version
  1. 365
Platform
  1. Windows
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?
 

JenniferMurphy

Well-known Member
Joined
Jul 23, 2011
Messages
1,438
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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.
 

Jeffrey Mahoney

Well-known Member
Joined
May 31, 2015
Messages
1,830
Office Version
  1. 365
Platform
  1. Windows
Try using the formulas I created for the named ranges.
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,961
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

@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.
 

Jeffrey Mahoney

Well-known Member
Joined
May 31, 2015
Messages
1,830
Office Version
  1. 365
Platform
  1. Windows
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.
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,961
Office Version
  1. 365
Platform
  1. Windows
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)
 

JenniferMurphy

Well-known Member
Joined
Jul 23, 2011
Messages
1,438
Office Version
  1. 365
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,336
Messages
5,624,093
Members
416,011
Latest member
chengkoonwing

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
Top