How to perform automatic graph range extension in Excel (with a constrain)
up vote
1
down vote
favorite
I'm having a hard time trying to make a graph's range extend automatically. I have made my research and I found this instructive:
https://www.officetooltips.com/excel_2016/tips/creating_automatically_extended_plot_ranges.html
It uses this formula to be applied while creating a "named range":
=OFFSET(<'Sheet name'>!<'start cell'>;0;0;COUNTA(<'Sheet name'>!<'Column name'>:<'Column name'strong text>) - 1)
The problem is the following:
This formula needs that the blank cells below the last data line written in the given table are totally blank (this is, blank... no formula in them whatsoever).
It works great if you manually input data in a blank table. Unfortunately, I need to gather the data from a secondary table which processes data from the first one (the one in which I really write down the data).
It looks that as this secondary table does contain a formula (even if it is made to show " " when no value is available), this method doesn't work.
Maybe I'm missing something obvious. I'm kind of a newbie, so any help would be great.
microsoft-excel charts range extension microsoft
edited Dec 3 at 20:58
Blackwood
2,72761727
asked Dec 3 at 20:39
Pablo
63
add a comment |
up vote
1
down vote
favorite
I'm having a hard time trying to make a graph's range extend automatically. I have made my research and I found this instructive:
https://www.officetooltips.com/excel_2016/tips/creating_automatically_extended_plot_ranges.html
It uses this formula to be applied while creating a "named range":
=OFFSET(<'Sheet name'>!<'start cell'>;0;0;COUNTA(<'Sheet name'>!<'Column name'>:<'Column name'strong text>) - 1)
The problem is the following:
This formula needs that the blank cells below the last data line written in the given table are totally blank (this is, blank... no formula in them whatsoever).
It works great if you manually input data in a blank table. Unfortunately, I need to gather the data from a secondary table which processes data from the first one (the one in which I really write down the data).
It looks that as this secondary table does contain a formula (even if it is made to show " " when no value is available), this method doesn't work.
Maybe I'm missing something obvious. I'm kind of a newbie, so any help would be great.
microsoft-excel charts range extension microsoft
edited Dec 3 at 20:58
Blackwood
2,72761727
asked Dec 3 at 20:39
Pablo
63
do you have a maximum row that you are applying your formula to? Is the formula being applied to the max row number 1048576 (Varies by version)
– Forward Ed
Dec 4 at 6:05
add a comment |
up vote
1
down vote
favorite
up vote
1
down vote
favorite
I'm having a hard time trying to make a graph's range extend automatically. I have made my research and I found this instructive:
https://www.officetooltips.com/excel_2016/tips/creating_automatically_extended_plot_ranges.html
It uses this formula to be applied while creating a "named range":
=OFFSET(<'Sheet name'>!<'start cell'>;0;0;COUNTA(<'Sheet name'>!<'Column name'>:<'Column name'strong text>) - 1)
The problem is the following:
This formula needs that the blank cells below the last data line written in the given table are totally blank (this is, blank... no formula in them whatsoever).
It works great if you manually input data in a blank table. Unfortunately, I need to gather the data from a secondary table which processes data from the first one (the one in which I really write down the data).
It looks that as this secondary table does contain a formula (even if it is made to show " " when no value is available), this method doesn't work.
Maybe I'm missing something obvious. I'm kind of a newbie, so any help would be great.
microsoft-excel charts range extension microsoft
edited Dec 3 at 20:58
Blackwood
2,72761727
asked Dec 3 at 20:39
Pablo
63
I'm having a hard time trying to make a graph's range extend automatically. I have made my research and I found this instructive:
https://www.officetooltips.com/excel_2016/tips/creating_automatically_extended_plot_ranges.html
It uses this formula to be applied while creating a "named range":
=OFFSET(<'Sheet name'>!<'start cell'>;0;0;COUNTA(<'Sheet name'>!<'Column name'>:<'Column name'strong text>) - 1)
The problem is the following:
This formula needs that the blank cells below the last data line written in the given table are totally blank (this is, blank... no formula in them whatsoever).
It works great if you manually input data in a blank table. Unfortunately, I need to gather the data from a secondary table which processes data from the first one (the one in which I really write down the data).
It looks that as this secondary table does contain a formula (even if it is made to show " " when no value is available), this method doesn't work.
Maybe I'm missing something obvious. I'm kind of a newbie, so any help would be great.
microsoft-excel charts range extension microsoft
microsoft-excel charts range extension microsoft
edited Dec 3 at 20:58
Blackwood
2,72761727
asked Dec 3 at 20:39
Pablo
63
edited Dec 3 at 20:58
Blackwood
2,72761727
asked Dec 3 at 20:39
Pablo
63
edited Dec 3 at 20:58
Blackwood
2,72761727
edited Dec 3 at 20:58
Blackwood
2,72761727
edited Dec 3 at 20:58
Blackwood
2,72761727
2,72761727
asked Dec 3 at 20:39
Pablo
63
asked Dec 3 at 20:39
Pablo
63
asked Dec 3 at 20:39
Pablo
63
63
do you have a maximum row that you are applying your formula to? Is the formula being applied to the max row number 1048576 (Varies by version)
– Forward Ed
Dec 4 at 6:05
add a comment |
do you have a maximum row that you are applying your formula to? Is the formula being applied to the max row number 1048576 (Varies by version)
– Forward Ed
Dec 4 at 6:05
do you have a maximum row that you are applying your formula to? Is the formula being applied to the max row number 1048576 (Varies by version)
– Forward Ed
Dec 4 at 6:05
do you have a maximum row that you are applying your formula to? Is the formula being applied to the max row number 1048576 (Varies by version)
– Forward Ed
Dec 4 at 6:05
add a comment |
active
oldest
votes
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',
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%2f1380513%2fhow-to-perform-automatic-graph-range-extension-in-excel-with-a-constrain%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
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.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- 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%2f1380513%2fhow-to-perform-automatic-graph-range-extension-in-excel-with-a-constrain%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 have a maximum row that you are applying your formula to? Is the formula being applied to the max row number 1048576 (Varies by version)
– Forward Ed
Dec 4 at 6:05