Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 481
  • Last Modified:

Excel Dynamic Range Name to update Pivot Table Source Data

I am attempting to dynamically update the range for a Named Range in Excel that is used by a Pivot Table.  I have seen some examples but mine is giving me an error {Reference Is not valid.}.

My data source table Starts in cell A4 and Ends in column H.  I named the range "PayDatesDataAll" and following is the Refers To: statement

PayDatesDataAll
="'Pay Dates Data'!$A$4:INDEX('Pay Dates Data'!$4:$1048576,COUNTA('Pay Dates Data'!$A:$A),COUNTA('Pay Dates Data'!$1:1))"

However, when I go to my Pivot Table (which currently just has a static range "a4:H6" and try to replace it with the named range PayDatesDataAll I get an error.  

Columns A, C, and H are formatted as Dates if it makes any difference
0
wlwebb
Asked:
wlwebb
  • 7
  • 7
  • 3
2 Solutions
 
SiddharthRoutCommented:
If you just type this formula in a cell

=INDEX('Pay Dates Data'!$4:$1048576,COUNTA('Pay Dates Data'!$A:$A),COUNTA('Pay Dates Data'!$1:1)

What is the value/error that you are getting?

Sid
0
 
SiddharthRoutCommented:
Sorry this (Bracket was missing)

=INDEX('Pay Dates Data'!$4:$1048576,COUNTA('Pay Dates Data'!$A:$A),COUNTA('Pay Dates Data'!$1:1))

Sid
0
 
wlwebbAuthor Commented:
Copied and pasted on the Pay Dates Data Table into cell j18
=INDEX('Pay Dates Data'!$4:$1048576,COUNTA('Pay Dates Data'!$A:$A),COUNTA('Pay Dates Data'!$1:1))

The answer I got was 0 (zero)

0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello,

since you start your index in A4, I assume you want to include all columns that have data in row 4, not row 1.

try

='Pay Dates Data'!$A$4:INDEX('Pay Dates Data'!$4:$1048576,COUNTA('Pay Dates Data'!$A:$A),COUNTA('Pay Dates Data'!$4:$4))

cheers, teylyn
0
 
wlwebbAuthor Commented:
If I do just CountA(a4:a15) I get 6 which is the number of lines that have data
0
 
SiddharthRoutCommented:
The answer I got was 0 (zero)

And hence the error. Check teylyns's formula.

Sid
0
 
SiddharthRoutCommented:
Can you post a sample workbook?

Sid
0
 
wlwebbAuthor Commented:
teylyn

My data starts in a4 and goes over to column H and then down however many rows have data
0
 
wlwebbAuthor Commented:
Sid
It's an Excel 2007 wkbk but I don't want to use the 2007 Tables feature
PR-Summary.xlsm
0
 
wlwebbAuthor Commented:
when I paste teylen's the following I still get 0


=INDEX('Pay Dates Data'!$4:$1048576,COUNTA('Pay Dates Data'!$A:$A),COUNTA('Pay Dates Data'!$4:$4))
0
 
wlwebbAuthor Commented:
Just got it to work with Teylyn's formula with one other variation.  When I went back to my Name Manager to paste her's in mine also had quotations (") at the beginning and end of the reference.  Once I removed those it works.

Thanks to both of you!
0
 
wlwebbAuthor Commented:
I split the points 300 Teylyn/200 Sid since both of you were very helpful but Teylyn's was the exact working reference.
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
When you copy and paste a formula from EE into the Refers to box of the Name Manager, it will always have " marks that you need to delete. It's not a flaw in the formula but due to the copy and paste from HTML.

cheers, teylyn
0
 
SiddharthRoutCommented:
Ah I see the error.

To understand it better Here is how Index Formula works...

It gets you the value in the intersection of the row and the column. However your

COUNTA('Pay Dates Data'!$A:$A)  = 12
and COUNTA('Pay Dates Data'!$1:1)=0

That is why it is not able to find a value.

If you use a basic index formula

=INDEX('Pay Dates Data'!$4:$1048576,11,8)

Then it will give you "41090"

The syntax of Index() is

INDEX(array,row_num,column_num)

Sid
0
 
SiddharthRoutCommented:
Oops I am late for the party :)

Sid
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
The formula was fine, basically. But it was counting in Row 1 for the last parameter instead of row 4, where the data to be counted actually sits.

cheers, teylyn
0
 
SiddharthRoutCommented:
Absolutely.

Sid
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 7
  • 7
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now