Solved

Excel Dynamic Range Name to update Pivot Table Source Data

Posted on 2011-02-13
17
440 Views
Last Modified: 2012-05-11
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
Comment
Question by:wlwebb
  • 7
  • 7
  • 3
17 Comments
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34883953
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
 
LVL 30

Assisted Solution

by:SiddharthRout
SiddharthRout earned 200 total points
ID: 34883957
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
 

Author Comment

by:wlwebb
ID: 34883964
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
 
LVL 50

Accepted Solution

by:
Ingeborg Hawighorst earned 300 total points
ID: 34883966
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
 

Author Comment

by:wlwebb
ID: 34883974
If I do just CountA(a4:a15) I get 6 which is the number of lines that have data
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34883975
The answer I got was 0 (zero)

And hence the error. Check teylyns's formula.

Sid
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34883977
Can you post a sample workbook?

Sid
0
 

Author Comment

by:wlwebb
ID: 34883979
teylyn

My data starts in a4 and goes over to column H and then down however many rows have data
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:wlwebb
ID: 34883983
Sid
It's an Excel 2007 wkbk but I don't want to use the 2007 Tables feature
PR-Summary.xlsm
0
 

Author Comment

by:wlwebb
ID: 34883987
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
 

Author Comment

by:wlwebb
ID: 34883994
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
 

Author Closing Comment

by:wlwebb
ID: 34884000
I split the points 300 Teylyn/200 Sid since both of you were very helpful but Teylyn's was the exact working reference.
0
 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 34884001
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
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34884011
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
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34884014
Oops I am late for the party :)

Sid
0
 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 34884033
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
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34884037
Absolutely.

Sid
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now