Calculated Fields Reference Error (#NAME?) on Pivot Table Refresh
I'm helping a colleague fix a spreadsheet with a similar issue as stated in this ozgrid post.
The problem: Pivot table with 31 calculated fields. Some calculated fields are calculations of the source data while others are calculations of the results of other calculated fields (this is the problematic part).
Example and the only workaround I have so far:
Total Sales $ TY ='Chain Sales Dlrs TY'+'WTD Sales TY'
Total LY Sales $ ='Chain Sales Dlrs LY'+'WTD Sales LY'
Chg Sales $ ='Total Sales $ TY'-'Total LY Sales $'
Total Sales $ TY and Total LY Sales $ use data source values, Chg Sales $ uses the results of Total Sales $ TY and Total LY Sales $ to calculate change. On changing the data source (e.g. changing values, column headings, insert columns, etc.), Chg Sales $ will give a #NAME? error on refresh. If I change Chg Sales $ to be a formula using data source references (i.e. =('Chain Sales Dlrs TY'+'WTD Sales TY')-('Chain Sales Dlrs LY'+'WTD Sales LY')), it works, but there are 20-30 of these to change.
Is there a way to use results of calculated fields in a calculated field and still have it refresh correctly in Excel 2010?
microsoft-excel microsoft-excel-2010 pivot-table
add a comment |
I'm helping a colleague fix a spreadsheet with a similar issue as stated in this ozgrid post.
The problem: Pivot table with 31 calculated fields. Some calculated fields are calculations of the source data while others are calculations of the results of other calculated fields (this is the problematic part).
Example and the only workaround I have so far:
Total Sales $ TY ='Chain Sales Dlrs TY'+'WTD Sales TY'
Total LY Sales $ ='Chain Sales Dlrs LY'+'WTD Sales LY'
Chg Sales $ ='Total Sales $ TY'-'Total LY Sales $'
Total Sales $ TY and Total LY Sales $ use data source values, Chg Sales $ uses the results of Total Sales $ TY and Total LY Sales $ to calculate change. On changing the data source (e.g. changing values, column headings, insert columns, etc.), Chg Sales $ will give a #NAME? error on refresh. If I change Chg Sales $ to be a formula using data source references (i.e. =('Chain Sales Dlrs TY'+'WTD Sales TY')-('Chain Sales Dlrs LY'+'WTD Sales LY')), it works, but there are 20-30 of these to change.
Is there a way to use results of calculated fields in a calculated field and still have it refresh correctly in Excel 2010?
microsoft-excel microsoft-excel-2010 pivot-table
Probably not much help to you, but having mocked this up on a small data set and adding your column names, I don't get the#NAME?error onChg Sales $. To fix this, think I'd start by looking to rebuild the file/pivot table from scratch in a new workbook (if that isn't too big/scary a job!)
– Andi Mohr
Dec 19 '14 at 13:44
Thanks for the tip! I actually handed off the spreadsheet with my workaround a couple days ago. I'll post an answer if I find a solution.
– user360767
Dec 19 '14 at 15:05
add a comment |
I'm helping a colleague fix a spreadsheet with a similar issue as stated in this ozgrid post.
The problem: Pivot table with 31 calculated fields. Some calculated fields are calculations of the source data while others are calculations of the results of other calculated fields (this is the problematic part).
Example and the only workaround I have so far:
Total Sales $ TY ='Chain Sales Dlrs TY'+'WTD Sales TY'
Total LY Sales $ ='Chain Sales Dlrs LY'+'WTD Sales LY'
Chg Sales $ ='Total Sales $ TY'-'Total LY Sales $'
Total Sales $ TY and Total LY Sales $ use data source values, Chg Sales $ uses the results of Total Sales $ TY and Total LY Sales $ to calculate change. On changing the data source (e.g. changing values, column headings, insert columns, etc.), Chg Sales $ will give a #NAME? error on refresh. If I change Chg Sales $ to be a formula using data source references (i.e. =('Chain Sales Dlrs TY'+'WTD Sales TY')-('Chain Sales Dlrs LY'+'WTD Sales LY')), it works, but there are 20-30 of these to change.
Is there a way to use results of calculated fields in a calculated field and still have it refresh correctly in Excel 2010?
microsoft-excel microsoft-excel-2010 pivot-table
I'm helping a colleague fix a spreadsheet with a similar issue as stated in this ozgrid post.
The problem: Pivot table with 31 calculated fields. Some calculated fields are calculations of the source data while others are calculations of the results of other calculated fields (this is the problematic part).
Example and the only workaround I have so far:
Total Sales $ TY ='Chain Sales Dlrs TY'+'WTD Sales TY'
Total LY Sales $ ='Chain Sales Dlrs LY'+'WTD Sales LY'
Chg Sales $ ='Total Sales $ TY'-'Total LY Sales $'
Total Sales $ TY and Total LY Sales $ use data source values, Chg Sales $ uses the results of Total Sales $ TY and Total LY Sales $ to calculate change. On changing the data source (e.g. changing values, column headings, insert columns, etc.), Chg Sales $ will give a #NAME? error on refresh. If I change Chg Sales $ to be a formula using data source references (i.e. =('Chain Sales Dlrs TY'+'WTD Sales TY')-('Chain Sales Dlrs LY'+'WTD Sales LY')), it works, but there are 20-30 of these to change.
Is there a way to use results of calculated fields in a calculated field and still have it refresh correctly in Excel 2010?
microsoft-excel microsoft-excel-2010 pivot-table
microsoft-excel microsoft-excel-2010 pivot-table
edited Dec 15 '14 at 20:18
asked Dec 15 '14 at 19:12
user360767
Probably not much help to you, but having mocked this up on a small data set and adding your column names, I don't get the#NAME?error onChg Sales $. To fix this, think I'd start by looking to rebuild the file/pivot table from scratch in a new workbook (if that isn't too big/scary a job!)
– Andi Mohr
Dec 19 '14 at 13:44
Thanks for the tip! I actually handed off the spreadsheet with my workaround a couple days ago. I'll post an answer if I find a solution.
– user360767
Dec 19 '14 at 15:05
add a comment |
Probably not much help to you, but having mocked this up on a small data set and adding your column names, I don't get the#NAME?error onChg Sales $. To fix this, think I'd start by looking to rebuild the file/pivot table from scratch in a new workbook (if that isn't too big/scary a job!)
– Andi Mohr
Dec 19 '14 at 13:44
Thanks for the tip! I actually handed off the spreadsheet with my workaround a couple days ago. I'll post an answer if I find a solution.
– user360767
Dec 19 '14 at 15:05
Probably not much help to you, but having mocked this up on a small data set and adding your column names, I don't get the
#NAME? error on Chg Sales $. To fix this, think I'd start by looking to rebuild the file/pivot table from scratch in a new workbook (if that isn't too big/scary a job!)– Andi Mohr
Dec 19 '14 at 13:44
Probably not much help to you, but having mocked this up on a small data set and adding your column names, I don't get the
#NAME? error on Chg Sales $. To fix this, think I'd start by looking to rebuild the file/pivot table from scratch in a new workbook (if that isn't too big/scary a job!)– Andi Mohr
Dec 19 '14 at 13:44
Thanks for the tip! I actually handed off the spreadsheet with my workaround a couple days ago. I'll post an answer if I find a solution.
– user360767
Dec 19 '14 at 15:05
Thanks for the tip! I actually handed off the spreadsheet with my workaround a couple days ago. I'll post an answer if I find a solution.
– user360767
Dec 19 '14 at 15:05
add a comment |
1 Answer
1
active
oldest
votes
Go to Pivot table Options, then check the box which says For error values show.
edited May 29 '15 at 9:50
Mureinik
2,57571625
answered May 29 '15 at 9:27
karthikkarthik
1
Short answers like this one are frowned upon in these sites. The reason is that there is nothing to learn from them. You should take the time to explain in some detail how your suggestion solves the problem at hand, so that future readers,when facing similar but distinct problems, can benefit from this very same anser.
– MariusMatutiae
May 29 '15 at 10:54
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f852923%2fcalculated-fields-reference-error-name-on-pivot-table-refresh%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
Go to Pivot table Options, then check the box which says For error values show.
edited May 29 '15 at 9:50
Mureinik
2,57571625
answered May 29 '15 at 9:27
karthikkarthik
1
Short answers like this one are frowned upon in these sites. The reason is that there is nothing to learn from them. You should take the time to explain in some detail how your suggestion solves the problem at hand, so that future readers,when facing similar but distinct problems, can benefit from this very same anser.
– MariusMatutiae
May 29 '15 at 10:54
add a comment |
Go to Pivot table Options, then check the box which says For error values show.
edited May 29 '15 at 9:50
Mureinik
2,57571625
answered May 29 '15 at 9:27
karthikkarthik
1
Short answers like this one are frowned upon in these sites. The reason is that there is nothing to learn from them. You should take the time to explain in some detail how your suggestion solves the problem at hand, so that future readers,when facing similar but distinct problems, can benefit from this very same anser.
– MariusMatutiae
May 29 '15 at 10:54
add a comment |
Go to Pivot table Options, then check the box which says For error values show.
edited May 29 '15 at 9:50
Mureinik
2,57571625
answered May 29 '15 at 9:27
karthikkarthik
1
Go to Pivot table Options, then check the box which says For error values show.
edited May 29 '15 at 9:50
Mureinik
2,57571625
answered May 29 '15 at 9:27
karthikkarthik
1
edited May 29 '15 at 9:50
Mureinik
2,57571625
edited May 29 '15 at 9:50
Mureinik
2,57571625
edited May 29 '15 at 9:50
Mureinik
2,57571625
2,57571625
answered May 29 '15 at 9:27
karthikkarthik
1
answered May 29 '15 at 9:27
karthikkarthik
1
answered May 29 '15 at 9:27
karthikkarthik
1
1
Short answers like this one are frowned upon in these sites. The reason is that there is nothing to learn from them. You should take the time to explain in some detail how your suggestion solves the problem at hand, so that future readers,when facing similar but distinct problems, can benefit from this very same anser.
– MariusMatutiae
May 29 '15 at 10:54
add a comment |
Short answers like this one are frowned upon in these sites. The reason is that there is nothing to learn from them. You should take the time to explain in some detail how your suggestion solves the problem at hand, so that future readers,when facing similar but distinct problems, can benefit from this very same anser.
– MariusMatutiae
May 29 '15 at 10:54
Short answers like this one are frowned upon in these sites. The reason is that there is nothing to learn from them. You should take the time to explain in some detail how your suggestion solves the problem at hand, so that future readers,when facing similar but distinct problems, can benefit from this very same anser.
– MariusMatutiae
May 29 '15 at 10:54
Short answers like this one are frowned upon in these sites. The reason is that there is nothing to learn from them. You should take the time to explain in some detail how your suggestion solves the problem at hand, so that future readers,when facing similar but distinct problems, can benefit from this very same anser.
– MariusMatutiae
May 29 '15 at 10:54
add a comment |
Thanks for contributing an answer to Super User!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f852923%2fcalculated-fields-reference-error-name-on-pivot-table-refresh%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Probably not much help to you, but having mocked this up on a small data set and adding your column names, I don't get the
#NAME?error onChg Sales $. To fix this, think I'd start by looking to rebuild the file/pivot table from scratch in a new workbook (if that isn't too big/scary a job!)– Andi Mohr
Dec 19 '14 at 13:44
Thanks for the tip! I actually handed off the spreadsheet with my workaround a couple days ago. I'll post an answer if I find a solution.
– user360767
Dec 19 '14 at 15:05