Update “overdue” formulas in Power Query
I have several files where I have several tasks. Each task has a "to be completed" date attached to it and a formula that calculates whether the task is "overdue" or "not yet due".
To generate a complete overview, I've created one Masterfile and used Power Query to link all these individual files. This works properly, however one issue I can't resolve which is the following:
The Power Query does not refresh the "overdue/not due" formula, when I am refreshing the Query. As such the Masterfile states that tasks that were "not due" at the time I saved the original file, are still not due, although they are in fact "overdue".. I think the Power Query read the original files without updating the formula's.. Is there anyway to resolve this?
excel excel-formula powerquery
asked Nov 21 '18 at 8:52
ErikSluiErikSlui
316
add a comment |
I have several files where I have several tasks. Each task has a "to be completed" date attached to it and a formula that calculates whether the task is "overdue" or "not yet due".
To generate a complete overview, I've created one Masterfile and used Power Query to link all these individual files. This works properly, however one issue I can't resolve which is the following:
The Power Query does not refresh the "overdue/not due" formula, when I am refreshing the Query. As such the Masterfile states that tasks that were "not due" at the time I saved the original file, are still not due, although they are in fact "overdue".. I think the Power Query read the original files without updating the formula's.. Is there anyway to resolve this?
excel excel-formula powerquery
asked Nov 21 '18 at 8:52
ErikSluiErikSlui
316
Do you still have the problem?
– Peter K.
Nov 27 '18 at 12:33
add a comment |
I have several files where I have several tasks. Each task has a "to be completed" date attached to it and a formula that calculates whether the task is "overdue" or "not yet due".
To generate a complete overview, I've created one Masterfile and used Power Query to link all these individual files. This works properly, however one issue I can't resolve which is the following:
The Power Query does not refresh the "overdue/not due" formula, when I am refreshing the Query. As such the Masterfile states that tasks that were "not due" at the time I saved the original file, are still not due, although they are in fact "overdue".. I think the Power Query read the original files without updating the formula's.. Is there anyway to resolve this?
excel excel-formula powerquery
asked Nov 21 '18 at 8:52
ErikSluiErikSlui
316
I have several files where I have several tasks. Each task has a "to be completed" date attached to it and a formula that calculates whether the task is "overdue" or "not yet due".
To generate a complete overview, I've created one Masterfile and used Power Query to link all these individual files. This works properly, however one issue I can't resolve which is the following:
The Power Query does not refresh the "overdue/not due" formula, when I am refreshing the Query. As such the Masterfile states that tasks that were "not due" at the time I saved the original file, are still not due, although they are in fact "overdue".. I think the Power Query read the original files without updating the formula's.. Is there anyway to resolve this?
excel excel-formula powerquery
excel excel-formula powerquery
asked Nov 21 '18 at 8:52
ErikSluiErikSlui
316
asked Nov 21 '18 at 8:52
ErikSluiErikSlui
316
asked Nov 21 '18 at 8:52
ErikSluiErikSlui
316
asked Nov 21 '18 at 8:52
ErikSluiErikSlui
316
asked Nov 21 '18 at 8:52
ErikSluiErikSlui
316
316
Do you still have the problem?
– Peter K.
Nov 27 '18 at 12:33
add a comment |
Do you still have the problem?
– Peter K.
Nov 27 '18 at 12:33
Do you still have the problem?
– Peter K.
Nov 27 '18 at 12:33
Do you still have the problem?
– Peter K.
Nov 27 '18 at 12:33
add a comment |
1 Answer
1
active
oldest
votes
There is no easy way to resolve this. Three possible options :
- If you want to accomplish this just from within your Masterfile, you can create a VBA script in your Masterfile that opens and closes all linked workbooks (see more info here). Please note that you must have access to these files (can be tricky if these are on a network drive or in the cloud);
- You also can just manually open and close the individual files before you open the Masterfile (or before you refresh the data);
- Change your approach/formulas, and use the individual files as pure data files, without any formula's that need refreshing, and do all calculations in the Masterfile.
My personal preference/recommendation would be 3), as I have some bad experiences from working with linked workbooks. My own approach if it is really necessary to exchange data between Excel files, is to create a Transfer tab/worksheet in the source workbook where you put all data that the other workbook needs, and create a Source tab/worksheet in the other workbook, and I copy/paste-as-values from one to the other, in particular when both workbooks are "in development". Once workbooks are stable (but does that ever happens?), you still can link the Target>Source worksheets (but auditing errors is just easier instead of having links all over your workbooks).
answered Nov 21 '18 at 10:43
Peter K.Peter K.
763212
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
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%2fstackoverflow.com%2fquestions%2f53408304%2fupdate-overdue-formulas-in-power-query%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
There is no easy way to resolve this. Three possible options :
- If you want to accomplish this just from within your Masterfile, you can create a VBA script in your Masterfile that opens and closes all linked workbooks (see more info here). Please note that you must have access to these files (can be tricky if these are on a network drive or in the cloud);
- You also can just manually open and close the individual files before you open the Masterfile (or before you refresh the data);
- Change your approach/formulas, and use the individual files as pure data files, without any formula's that need refreshing, and do all calculations in the Masterfile.
My personal preference/recommendation would be 3), as I have some bad experiences from working with linked workbooks. My own approach if it is really necessary to exchange data between Excel files, is to create a Transfer tab/worksheet in the source workbook where you put all data that the other workbook needs, and create a Source tab/worksheet in the other workbook, and I copy/paste-as-values from one to the other, in particular when both workbooks are "in development". Once workbooks are stable (but does that ever happens?), you still can link the Target>Source worksheets (but auditing errors is just easier instead of having links all over your workbooks).
answered Nov 21 '18 at 10:43
Peter K.Peter K.
763212
add a comment |
There is no easy way to resolve this. Three possible options :
- If you want to accomplish this just from within your Masterfile, you can create a VBA script in your Masterfile that opens and closes all linked workbooks (see more info here). Please note that you must have access to these files (can be tricky if these are on a network drive or in the cloud);
- You also can just manually open and close the individual files before you open the Masterfile (or before you refresh the data);
- Change your approach/formulas, and use the individual files as pure data files, without any formula's that need refreshing, and do all calculations in the Masterfile.
My personal preference/recommendation would be 3), as I have some bad experiences from working with linked workbooks. My own approach if it is really necessary to exchange data between Excel files, is to create a Transfer tab/worksheet in the source workbook where you put all data that the other workbook needs, and create a Source tab/worksheet in the other workbook, and I copy/paste-as-values from one to the other, in particular when both workbooks are "in development". Once workbooks are stable (but does that ever happens?), you still can link the Target>Source worksheets (but auditing errors is just easier instead of having links all over your workbooks).
answered Nov 21 '18 at 10:43
Peter K.Peter K.
763212
add a comment |
There is no easy way to resolve this. Three possible options :
- If you want to accomplish this just from within your Masterfile, you can create a VBA script in your Masterfile that opens and closes all linked workbooks (see more info here). Please note that you must have access to these files (can be tricky if these are on a network drive or in the cloud);
- You also can just manually open and close the individual files before you open the Masterfile (or before you refresh the data);
- Change your approach/formulas, and use the individual files as pure data files, without any formula's that need refreshing, and do all calculations in the Masterfile.
My personal preference/recommendation would be 3), as I have some bad experiences from working with linked workbooks. My own approach if it is really necessary to exchange data between Excel files, is to create a Transfer tab/worksheet in the source workbook where you put all data that the other workbook needs, and create a Source tab/worksheet in the other workbook, and I copy/paste-as-values from one to the other, in particular when both workbooks are "in development". Once workbooks are stable (but does that ever happens?), you still can link the Target>Source worksheets (but auditing errors is just easier instead of having links all over your workbooks).
answered Nov 21 '18 at 10:43
Peter K.Peter K.
763212
There is no easy way to resolve this. Three possible options :
- If you want to accomplish this just from within your Masterfile, you can create a VBA script in your Masterfile that opens and closes all linked workbooks (see more info here). Please note that you must have access to these files (can be tricky if these are on a network drive or in the cloud);
- You also can just manually open and close the individual files before you open the Masterfile (or before you refresh the data);
- Change your approach/formulas, and use the individual files as pure data files, without any formula's that need refreshing, and do all calculations in the Masterfile.
My personal preference/recommendation would be 3), as I have some bad experiences from working with linked workbooks. My own approach if it is really necessary to exchange data between Excel files, is to create a Transfer tab/worksheet in the source workbook where you put all data that the other workbook needs, and create a Source tab/worksheet in the other workbook, and I copy/paste-as-values from one to the other, in particular when both workbooks are "in development". Once workbooks are stable (but does that ever happens?), you still can link the Target>Source worksheets (but auditing errors is just easier instead of having links all over your workbooks).
answered Nov 21 '18 at 10:43
Peter K.Peter K.
763212
edited Nov 21 '18 at 11:47
answered Nov 21 '18 at 10:43
Peter K.Peter K.
763212
answered Nov 21 '18 at 10:43
Peter K.Peter K.
763212
answered Nov 21 '18 at 10:43
Peter K.Peter K.
763212
763212
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- 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%2fstackoverflow.com%2fquestions%2f53408304%2fupdate-overdue-formulas-in-power-query%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
Do you still have the problem?
– Peter K.
Nov 27 '18 at 12:33