Google Sheets - stretch formula down column, but reuse input from same cell
Let me start off by emphasizing that this is NOT an excel question. This is a Google Sheets question.
On line 19 I have entered a formula into the cell of a google sheets spreadsheet that looks like this:
=(E19/B5)
I then drag/stretch down the formula to populate the cells below it in the same column.
Here is the issue. As I drag the cell down to populate the cells below it, the following cells should automatically populate as such:
Line 20 should have the following formula: =(E20/B5)
Line 21 should have the following formula: =(E21/B5)
Line 22 should have the following formula: =(E22/B5)
Line 23 should have the following formula: =(E23/B5)
...
Notice how a reference to B5 should be in each cell.
Instead, this is what I get. Please understand that this is what I do not want to happen:
Line 20 incorrectly has the formula: =(E20/B5)
Line 21 incorrectly has the formula::=(E21/B6)
Line 22 incorrectly has the formula:=(E22/B7)
Line 23 incorrectly has the formula:=(E23/B8)
...
To flog a horse, all formulas should contain the reference to B5. What formula do I need to enter so that it always references B5 as I drag/stretch the formula down the column?
google-sheets
edited Dec 6 at 7:07
serenesat
8,54792042
asked Dec 5 at 20:32
John R
8318
migrated from superuser.com Dec 5 at 21:20
This question came from our site for computer enthusiasts and power users.
add a comment |
Let me start off by emphasizing that this is NOT an excel question. This is a Google Sheets question.
On line 19 I have entered a formula into the cell of a google sheets spreadsheet that looks like this:
=(E19/B5)
I then drag/stretch down the formula to populate the cells below it in the same column.
Here is the issue. As I drag the cell down to populate the cells below it, the following cells should automatically populate as such:
Line 20 should have the following formula: =(E20/B5)
Line 21 should have the following formula: =(E21/B5)
Line 22 should have the following formula: =(E22/B5)
Line 23 should have the following formula: =(E23/B5)
...
Notice how a reference to B5 should be in each cell.
Instead, this is what I get. Please understand that this is what I do not want to happen:
Line 20 incorrectly has the formula: =(E20/B5)
Line 21 incorrectly has the formula::=(E21/B6)
Line 22 incorrectly has the formula:=(E22/B7)
Line 23 incorrectly has the formula:=(E23/B8)
...
To flog a horse, all formulas should contain the reference to B5. What formula do I need to enter so that it always references B5 as I drag/stretch the formula down the column?
google-sheets
edited Dec 6 at 7:07
serenesat
8,54792042
asked Dec 5 at 20:32
John R
8318
migrated from superuser.com Dec 5 at 21:20
This question came from our site for computer enthusiasts and power users.
1
Apologies to the group, this is a duplicate question. The answer is here: superuser.com/questions/1346678/…
– John R
Dec 5 at 20:57
add a comment |
Let me start off by emphasizing that this is NOT an excel question. This is a Google Sheets question.
On line 19 I have entered a formula into the cell of a google sheets spreadsheet that looks like this:
=(E19/B5)
I then drag/stretch down the formula to populate the cells below it in the same column.
Here is the issue. As I drag the cell down to populate the cells below it, the following cells should automatically populate as such:
Line 20 should have the following formula: =(E20/B5)
Line 21 should have the following formula: =(E21/B5)
Line 22 should have the following formula: =(E22/B5)
Line 23 should have the following formula: =(E23/B5)
...
Notice how a reference to B5 should be in each cell.
Instead, this is what I get. Please understand that this is what I do not want to happen:
Line 20 incorrectly has the formula: =(E20/B5)
Line 21 incorrectly has the formula::=(E21/B6)
Line 22 incorrectly has the formula:=(E22/B7)
Line 23 incorrectly has the formula:=(E23/B8)
...
To flog a horse, all formulas should contain the reference to B5. What formula do I need to enter so that it always references B5 as I drag/stretch the formula down the column?
google-sheets
edited Dec 6 at 7:07
serenesat
8,54792042
asked Dec 5 at 20:32
John R
8318
Let me start off by emphasizing that this is NOT an excel question. This is a Google Sheets question.
On line 19 I have entered a formula into the cell of a google sheets spreadsheet that looks like this:
=(E19/B5)
I then drag/stretch down the formula to populate the cells below it in the same column.
Here is the issue. As I drag the cell down to populate the cells below it, the following cells should automatically populate as such:
Line 20 should have the following formula: =(E20/B5)
Line 21 should have the following formula: =(E21/B5)
Line 22 should have the following formula: =(E22/B5)
Line 23 should have the following formula: =(E23/B5)
...
Notice how a reference to B5 should be in each cell.
Instead, this is what I get. Please understand that this is what I do not want to happen:
Line 20 incorrectly has the formula: =(E20/B5)
Line 21 incorrectly has the formula::=(E21/B6)
Line 22 incorrectly has the formula:=(E22/B7)
Line 23 incorrectly has the formula:=(E23/B8)
...
To flog a horse, all formulas should contain the reference to B5. What formula do I need to enter so that it always references B5 as I drag/stretch the formula down the column?
google-sheets
google-sheets
edited Dec 6 at 7:07
serenesat
8,54792042
asked Dec 5 at 20:32
John R
8318
edited Dec 6 at 7:07
serenesat
8,54792042
asked Dec 5 at 20:32
John R
8318
edited Dec 6 at 7:07
serenesat
8,54792042
edited Dec 6 at 7:07
serenesat
8,54792042
edited Dec 6 at 7:07
serenesat
8,54792042
8,54792042
asked Dec 5 at 20:32
John R
8318
asked Dec 5 at 20:32
John R
8318
asked Dec 5 at 20:32
John R
8318
8318
migrated from superuser.com Dec 5 at 21:20
This question came from our site for computer enthusiasts and power users.
migrated from superuser.com Dec 5 at 21:20
This question came from our site for computer enthusiasts and power users.
1
Apologies to the group, this is a duplicate question. The answer is here: superuser.com/questions/1346678/…
– John R
Dec 5 at 20:57
add a comment |
1
Apologies to the group, this is a duplicate question. The answer is here: superuser.com/questions/1346678/…
– John R
Dec 5 at 20:57
1
1
Apologies to the group, this is a duplicate question. The answer is here: superuser.com/questions/1346678/…
– John R
Dec 5 at 20:57
Apologies to the group, this is a duplicate question. The answer is here: superuser.com/questions/1346678/…
– John R
Dec 5 at 20:57
add a comment |
1 Answer
1
active
oldest
votes
You need to "lock" the row portion of your B5 reference with a dollar sign in the row-19 version:
=(E19/B$5)
However, since the formula is repetitious, you can construct it as a single array formula without the need to drag-copy it down. If you'd like to explore that approach, share a link to your sheet (or a copy of it) here. In the meantime, the above change to the formula will allow you to drag-copy without the B5 reference changing.
answered Dec 8 at 15:19
Erik Tyler
5196
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "34"
};
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: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
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
},
noCode: 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%2fwebapps.stackexchange.com%2fquestions%2f122797%2fgoogle-sheets-stretch-formula-down-column-but-reuse-input-from-same-cell%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
You need to "lock" the row portion of your B5 reference with a dollar sign in the row-19 version:
=(E19/B$5)
However, since the formula is repetitious, you can construct it as a single array formula without the need to drag-copy it down. If you'd like to explore that approach, share a link to your sheet (or a copy of it) here. In the meantime, the above change to the formula will allow you to drag-copy without the B5 reference changing.
answered Dec 8 at 15:19
Erik Tyler
5196
add a comment |
You need to "lock" the row portion of your B5 reference with a dollar sign in the row-19 version:
=(E19/B$5)
However, since the formula is repetitious, you can construct it as a single array formula without the need to drag-copy it down. If you'd like to explore that approach, share a link to your sheet (or a copy of it) here. In the meantime, the above change to the formula will allow you to drag-copy without the B5 reference changing.
answered Dec 8 at 15:19
Erik Tyler
5196
add a comment |
You need to "lock" the row portion of your B5 reference with a dollar sign in the row-19 version:
=(E19/B$5)
However, since the formula is repetitious, you can construct it as a single array formula without the need to drag-copy it down. If you'd like to explore that approach, share a link to your sheet (or a copy of it) here. In the meantime, the above change to the formula will allow you to drag-copy without the B5 reference changing.
answered Dec 8 at 15:19
Erik Tyler
5196
You need to "lock" the row portion of your B5 reference with a dollar sign in the row-19 version:
=(E19/B$5)
However, since the formula is repetitious, you can construct it as a single array formula without the need to drag-copy it down. If you'd like to explore that approach, share a link to your sheet (or a copy of it) here. In the meantime, the above change to the formula will allow you to drag-copy without the B5 reference changing.
answered Dec 8 at 15:19
Erik Tyler
5196
answered Dec 8 at 15:19
Erik Tyler
5196
answered Dec 8 at 15:19
Erik Tyler
5196
answered Dec 8 at 15:19
Erik Tyler
5196
5196
add a comment |
add a comment |
Thanks for contributing an answer to Web Applications Stack Exchange!
- 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%2fwebapps.stackexchange.com%2fquestions%2f122797%2fgoogle-sheets-stretch-formula-down-column-but-reuse-input-from-same-cell%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
1
Apologies to the group, this is a duplicate question. The answer is here: superuser.com/questions/1346678/…
– John R
Dec 5 at 20:57