Incremental(?) or offset(?) formula EXCEL
I need help with something. I have a list of values (in sheet 2) and I want to create two rows (where A4 = A3) of said values in sheet 1. (Shown below) Is there a formula I can use in Excel that can allow me to copy that formula in sheet one across hundreds of rows?
microsoft-excel worksheet-function microsoft-excel-2010
edited Feb 5 at 21:28
cybernetic.nomad
2,386517
asked Feb 5 at 19:23
nesecito ayudanesecito ayuda
12
add a comment |
I need help with something. I have a list of values (in sheet 2) and I want to create two rows (where A4 = A3) of said values in sheet 1. (Shown below) Is there a formula I can use in Excel that can allow me to copy that formula in sheet one across hundreds of rows?
microsoft-excel worksheet-function microsoft-excel-2010
edited Feb 5 at 21:28
cybernetic.nomad
2,386517
asked Feb 5 at 19:23
nesecito ayudanesecito ayuda
12
Will the values in Sheet 1 always come in pairs?
– cybernetic.nomad
Feb 5 at 19:36
i want to populate two rows on sheet one based on one cell from sheet 2
– nesecito ayuda
Feb 5 at 19:38
add a comment |
I need help with something. I have a list of values (in sheet 2) and I want to create two rows (where A4 = A3) of said values in sheet 1. (Shown below) Is there a formula I can use in Excel that can allow me to copy that formula in sheet one across hundreds of rows?
microsoft-excel worksheet-function microsoft-excel-2010
edited Feb 5 at 21:28
cybernetic.nomad
2,386517
asked Feb 5 at 19:23
nesecito ayudanesecito ayuda
12
I need help with something. I have a list of values (in sheet 2) and I want to create two rows (where A4 = A3) of said values in sheet 1. (Shown below) Is there a formula I can use in Excel that can allow me to copy that formula in sheet one across hundreds of rows?
microsoft-excel worksheet-function microsoft-excel-2010
microsoft-excel worksheet-function microsoft-excel-2010
edited Feb 5 at 21:28
cybernetic.nomad
2,386517
asked Feb 5 at 19:23
nesecito ayudanesecito ayuda
12
edited Feb 5 at 21:28
cybernetic.nomad
2,386517
asked Feb 5 at 19:23
nesecito ayudanesecito ayuda
12
edited Feb 5 at 21:28
cybernetic.nomad
2,386517
edited Feb 5 at 21:28
cybernetic.nomad
2,386517
edited Feb 5 at 21:28
cybernetic.nomad
2,386517
2,386517
asked Feb 5 at 19:23
nesecito ayudanesecito ayuda
12
asked Feb 5 at 19:23
nesecito ayudanesecito ayuda
12
asked Feb 5 at 19:23
nesecito ayudanesecito ayuda
12
12
Will the values in Sheet 1 always come in pairs?
– cybernetic.nomad
Feb 5 at 19:36
i want to populate two rows on sheet one based on one cell from sheet 2
– nesecito ayuda
Feb 5 at 19:38
add a comment |
Will the values in Sheet 1 always come in pairs?
– cybernetic.nomad
Feb 5 at 19:36
i want to populate two rows on sheet one based on one cell from sheet 2
– nesecito ayuda
Feb 5 at 19:38
Will the values in Sheet 1 always come in pairs?
– cybernetic.nomad
Feb 5 at 19:36
Will the values in Sheet 1 always come in pairs?
– cybernetic.nomad
Feb 5 at 19:36
i want to populate two rows on sheet one based on one cell from sheet 2
– nesecito ayuda
Feb 5 at 19:38
i want to populate two rows on sheet one based on one cell from sheet 2
– nesecito ayuda
Feb 5 at 19:38
add a comment |
2 Answers
2
active
oldest
votes
If you want to avoid the volaitle INDIRECT you can use INDEX:
=INDEX(Sheet1!A:A,INT((ROW(1:1)-1)/2)+2)
Where the +2 is the starting row of the data on Sheet1. I assumed row 2, if different change to the actual first row of data.
answered Feb 5 at 20:18
Scott CranerScott Craner
12.3k11218
So much more clever than my answer
– cybernetic.nomad
Feb 5 at 20:35
add a comment |
Assuming the data in sheet 1 is in column A
in Sheet 2, A1, add the following formula:
=INDIRECT("Sheet1!A" & ROW()*2)
And in B1 add this one:
=INDIRECT("Sheet1!A" & ROW()*2-1)
Then populate down
answered Feb 5 at 19:40
cybernetic.nomadcybernetic.nomad
2,386517
1
There is nothing wrong with this, I just dislike the unnecessary re-calc of multiple formula if it can be avoided.
– Scott Craner
Feb 5 at 20:38
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%2f1402377%2fincremental-or-offset-formula-excel%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
If you want to avoid the volaitle INDIRECT you can use INDEX:
=INDEX(Sheet1!A:A,INT((ROW(1:1)-1)/2)+2)
Where the +2 is the starting row of the data on Sheet1. I assumed row 2, if different change to the actual first row of data.
answered Feb 5 at 20:18
Scott CranerScott Craner
12.3k11218
So much more clever than my answer
– cybernetic.nomad
Feb 5 at 20:35
add a comment |
If you want to avoid the volaitle INDIRECT you can use INDEX:
=INDEX(Sheet1!A:A,INT((ROW(1:1)-1)/2)+2)
Where the +2 is the starting row of the data on Sheet1. I assumed row 2, if different change to the actual first row of data.
answered Feb 5 at 20:18
Scott CranerScott Craner
12.3k11218
So much more clever than my answer
– cybernetic.nomad
Feb 5 at 20:35
add a comment |
If you want to avoid the volaitle INDIRECT you can use INDEX:
=INDEX(Sheet1!A:A,INT((ROW(1:1)-1)/2)+2)
Where the +2 is the starting row of the data on Sheet1. I assumed row 2, if different change to the actual first row of data.
answered Feb 5 at 20:18
Scott CranerScott Craner
12.3k11218
If you want to avoid the volaitle INDIRECT you can use INDEX:
=INDEX(Sheet1!A:A,INT((ROW(1:1)-1)/2)+2)
Where the +2 is the starting row of the data on Sheet1. I assumed row 2, if different change to the actual first row of data.
answered Feb 5 at 20:18
Scott CranerScott Craner
12.3k11218
answered Feb 5 at 20:18
Scott CranerScott Craner
12.3k11218
answered Feb 5 at 20:18
Scott CranerScott Craner
12.3k11218
answered Feb 5 at 20:18
Scott CranerScott Craner
12.3k11218
12.3k11218
So much more clever than my answer
– cybernetic.nomad
Feb 5 at 20:35
add a comment |
So much more clever than my answer
– cybernetic.nomad
Feb 5 at 20:35
So much more clever than my answer
– cybernetic.nomad
Feb 5 at 20:35
So much more clever than my answer
– cybernetic.nomad
Feb 5 at 20:35
add a comment |
Assuming the data in sheet 1 is in column A
in Sheet 2, A1, add the following formula:
=INDIRECT("Sheet1!A" & ROW()*2)
And in B1 add this one:
=INDIRECT("Sheet1!A" & ROW()*2-1)
Then populate down
answered Feb 5 at 19:40
cybernetic.nomadcybernetic.nomad
2,386517
1
There is nothing wrong with this, I just dislike the unnecessary re-calc of multiple formula if it can be avoided.
– Scott Craner
Feb 5 at 20:38
add a comment |
Assuming the data in sheet 1 is in column A
in Sheet 2, A1, add the following formula:
=INDIRECT("Sheet1!A" & ROW()*2)
And in B1 add this one:
=INDIRECT("Sheet1!A" & ROW()*2-1)
Then populate down
answered Feb 5 at 19:40
cybernetic.nomadcybernetic.nomad
2,386517
1
There is nothing wrong with this, I just dislike the unnecessary re-calc of multiple formula if it can be avoided.
– Scott Craner
Feb 5 at 20:38
add a comment |
Assuming the data in sheet 1 is in column A
in Sheet 2, A1, add the following formula:
=INDIRECT("Sheet1!A" & ROW()*2)
And in B1 add this one:
=INDIRECT("Sheet1!A" & ROW()*2-1)
Then populate down
answered Feb 5 at 19:40
cybernetic.nomadcybernetic.nomad
2,386517
Assuming the data in sheet 1 is in column A
in Sheet 2, A1, add the following formula:
=INDIRECT("Sheet1!A" & ROW()*2)
And in B1 add this one:
=INDIRECT("Sheet1!A" & ROW()*2-1)
Then populate down
answered Feb 5 at 19:40
cybernetic.nomadcybernetic.nomad
2,386517
answered Feb 5 at 19:40
cybernetic.nomadcybernetic.nomad
2,386517
answered Feb 5 at 19:40
cybernetic.nomadcybernetic.nomad
2,386517
answered Feb 5 at 19:40
cybernetic.nomadcybernetic.nomad
2,386517
2,386517
1
There is nothing wrong with this, I just dislike the unnecessary re-calc of multiple formula if it can be avoided.
– Scott Craner
Feb 5 at 20:38
add a comment |
1
There is nothing wrong with this, I just dislike the unnecessary re-calc of multiple formula if it can be avoided.
– Scott Craner
Feb 5 at 20:38
1
1
There is nothing wrong with this, I just dislike the unnecessary re-calc of multiple formula if it can be avoided.
– Scott Craner
Feb 5 at 20:38
There is nothing wrong with this, I just dislike the unnecessary re-calc of multiple formula if it can be avoided.
– Scott Craner
Feb 5 at 20:38
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%2f1402377%2fincremental-or-offset-formula-excel%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
Will the values in Sheet 1 always come in pairs?
– cybernetic.nomad
Feb 5 at 19:36
i want to populate two rows on sheet one based on one cell from sheet 2
– nesecito ayuda
Feb 5 at 19:38