Pivottable source data keeps changing

thomasb82

New Member
Joined
Nov 22, 2016
Messages
12
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!
 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,808
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Nov 22, 2016
Messages
12
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

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,808
Office Version
  1. 365
Platform
  1. Windows
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.
 
Last edited:

thomasb82

New Member
Joined
Nov 22, 2016
Messages
12

ADVERTISEMENT

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

thomasb82

New Member
Joined
Nov 22, 2016
Messages
12
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)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,721
Messages
5,597,758
Members
414,171
Latest member
12Rev79

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