Question description:
This user has given permission to use the problem statement for this
blog.
can not turn my data into a pivot table... reference invalid
Solved by I. B. in 39 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
06/08/2018 - 05:16
hello..
User
06/08/2018 - 05:16
hello0
User
06/08/2018 - 05:16
let me send you the file
Excelchat Expert
06/08/2018 - 05:16
ok..
User
06/08/2018 - 05:17
[Uploaded an Excel file]
User
06/08/2018 - 05:17
I want to make a pivot table of 2017 sheet and if I select all the data it says the reference is invalid when creating a pivot tbale
User
06/08/2018 - 05:17
I dont know why
Excelchat Expert
06/08/2018 - 05:18
hold on.. let me look at it..
Excelchat Expert
06/08/2018 - 05:19
do you want to make pivot table from A to K columns?
Excelchat Expert
06/08/2018 - 05:19
on 2017 sheet..??
User
06/08/2018 - 05:19
yes
User
06/08/2018 - 05:19
or at least tell me why its not working when I do it so I can do it mysekf
Excelchat Expert
06/08/2018 - 05:21
don't have any problem to make pivot table..
Excelchat Expert
06/08/2018 - 05:21
hold on..
User
06/08/2018 - 05:21
what the heck
User
06/08/2018 - 05:22
yeah I have no idea whats happening on my end
User
06/08/2018 - 05:22
this usually isn't difficult
User
06/08/2018 - 05:22
I was wondering if the file was corrupted or something
Excelchat Expert
06/08/2018 - 05:22
hold on..
Excelchat Expert
06/08/2018 - 05:22
let me check somthing..
User
06/08/2018 - 05:22
it says data source reference is not valid
User
06/08/2018 - 05:22
no idea why
Excelchat Expert
06/08/2018 - 05:24
you already have 2017 Pivot..
Excelchat Expert
06/08/2018 - 05:24
do you need this sheet..??
User
06/08/2018 - 05:24
yeah I do
User
06/08/2018 - 05:24
I want to make two separate pivots
User
06/08/2018 - 05:24
is that not possible?
Excelchat Expert
06/08/2018 - 05:24
yes possible but this pivot source it not this file..
User
06/08/2018 - 05:25
???
User
06/08/2018 - 05:25
what do you mean
Excelchat Expert
06/08/2018 - 05:25
current 2017 Pivot was made from [branchlessbanking (1).xlsx]2017'!$B:$M
User
06/08/2018 - 05:26
oh
User
06/08/2018 - 05:26
that makes sense
User
06/08/2018 - 05:26
but
User
06/08/2018 - 05:26
why does that effect me making a. pivot from 2017
Excelchat Expert
06/08/2018 - 05:26
no .. only problem is I can't unload new file after I made pivot from 2017 sheet because it exceed 20M limit..
User
06/08/2018 - 05:27
so what do you recommend I do
User
06/08/2018 - 05:27
just create a new document with the same Data and go??
Excelchat Expert
06/08/2018 - 05:27
let try other way..
User
06/08/2018 - 05:27
ok
User
06/08/2018 - 05:28
let me know
Excelchat Expert
06/08/2018 - 05:28
what version of excel are you using..
User
06/08/2018 - 05:28
16.15
User
06/08/2018 - 05:28
for mac
Excelchat Expert
06/08/2018 - 05:29
hold on..
User
06/08/2018 - 05:32
ok
Excelchat Expert
06/08/2018 - 05:33
I was trying upload file for you.. but no success..
User
06/08/2018 - 05:33
too large?
User
06/08/2018 - 05:33
do you want to send via email?
Excelchat Expert
06/08/2018 - 05:34
company is not allow exchange email address..
User
06/08/2018 - 05:34
ok
Excelchat Expert
06/08/2018 - 05:34
sorry about that..
User
06/08/2018 - 05:34
so lets do a solution
User
06/08/2018 - 05:34
thats fair not your policy
User
06/08/2018 - 05:35
frustrating though
Excelchat Expert
06/08/2018 - 05:35
ok..
User
06/08/2018 - 05:35
what did you do to fix it
User
06/08/2018 - 05:35
so I can follow the same steps
Excelchat Expert
06/08/2018 - 05:35
first select area A1:K89858
User
06/08/2018 - 05:35
ok
Excelchat Expert
06/08/2018 - 05:36
click Insert>PivotTable
User
06/08/2018 - 05:37
there is no pivotable option under insert
Excelchat Expert
06/08/2018 - 05:37
hold on..
Excelchat Expert
06/08/2018 - 05:37
how do you make pivot table in Mac..
User
06/08/2018 - 05:37
data --> summarize with pivot table
User
06/08/2018 - 05:38
I can do insert pivot chart
User
06/08/2018 - 05:38
but thats not what I wan
User
06/08/2018 - 05:38
want
Excelchat Expert
06/08/2018 - 05:39
I am trying to find out why you have error by following your steps..
User
06/08/2018 - 05:39
I do not know why it says the Data source is not valid
User
06/08/2018 - 05:40
on the Microsoft help site it says bc their may be brackets in the file name that are invalid
User
06/08/2018 - 05:40
but I dont think theres anything invalid about my file name
Excelchat Expert
06/08/2018 - 05:41
I think you are trying to making from other data source which is not found..
User
06/08/2018 - 05:41
I changed the file name and it work
User
06/08/2018 - 05:41
worked
Excelchat Expert
06/08/2018 - 05:42
did you changed name " [branchlessbanking (1).xlsx]2017'!$B:$M"
User
06/08/2018 - 05:43
I just added a space between branchless banking and it worked
Excelchat Expert
06/08/2018 - 05:43
I am happy that you figure it out..
Excelchat Expert
06/08/2018 - 05:45
[Uploaded an Excel file]
Excelchat Expert
06/08/2018 - 05:45
I eliminate 2008..and made pivot from 2017
Excelchat Expert
06/08/2018 - 05:46
I think you hare choosing other data source when you making pviot table..
Excelchat Expert
06/08/2018 - 05:47
if you want to make pivot from sheet.. you need assign area from that sheet..
Excelchat Expert
06/08/2018 - 05:47
and you will not have problem to making pivot table..
Excelchat Expert
06/08/2018 - 05:47
Do you have any other questions..??
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
