Pivottable source data keeps changing

[thomasb82]

Hi, i've got this persistent problem in my excel file that suddenly started appearing:
got a huge document with several columns and data. Using pivottables to get statistics(median age etc).
The pivottable pick up data from the entire sheet, like this "Aktuellt!$A$1:$AD$999"

however, when i open the files and try to update the tables, the source data references has changed to something else, like "Aktuellt!$A$16:$AD$16" (only one row). So i get an error message saying theres not enough rows to collect data from. I then have to redo all 32343 tables...

Anyone know why the source data reference in the pivottable changes? Could it be cos some users(shared document) keeps deleting rows?

thanks!

 

[Jon von der Heyden]

It could be that users are deleting / adding rows, Yes.

I recommend that you hold the data in a table, or create a dynamic named range.

To help you, can you please tell me which column of data you are assured will house data in the last record of the table, and will that column be number or text?

 

[thomasb82]

hey jon, thanks for your answer. not sure if understand you correctly, the sheet i'm picking data from has 24 columns, contatining both numbers, dates and text. each row contains data on a singe person(age, name etc). Users then add rows themselves, with new people. So i guess a dynamic range would work perhaps, since they keep adding rows?

 

[Jon von der Heyden]

What I'm trying to determine is which lead column is best suited to determine where your last record lies. So I assume that a record MUST contain a last name, or perhaps an ID referring to the person? So on this basis, lets assume this is in column A.

Go to Name Manager and create a new name.
Name: pvt_PeopleData
Refers to: =INDEX(Aktuellt!$A$:$A,1,1):INDEX(Aktuellt!$AD$:$AD,MATCH(REPT("Z",255),Aktuellt!$A$:$A,1)),1)

Then you can refer to the named range in your pivot source. E.g. =pvt_PeopleData

In terms of the red bits:
1. I am assuming we are looking for the last text entry in column A. If you choose a lead coulmn on numeric type, then substitute the REPT() with 10^308.
2. I have chosen column A as the lead column. Change this to suit.

 

[thomasb82]

ah, i think i understand now, will try it out. thanks a lot!

 

[thomasb82]

hm...tricky.. Column A contains dates, is formated as such as well. so the forumla should use the 10^308 then i assume?, i get an formula error though trying to use this:

=INDEX(Aktuellt!C$:$C,1,1):INDEX(Aktuellt!$AD$:$AD,MATCH(10^308("Z",255),Aktuellt!$A$:$A,1)),1)

 

[Jon von der Heyden]

Hi

This: 10^308("Z",255)

should be: 10^308