Referencing a single row to multiple rows in Excel
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
I have a worksheet with a list of sales, one sale per row. In order to import this data into my accounting software, I need to create a separate worksheet that formats the data differently. Each row from the first worksheet will take up four rows in the second worksheet.
I set up my four rows in the second worksheet and make all the relative references to the first row in the first worksheet. When I make a copy of the four rows the cell references are incorrect. For example, the first four rows should reference worksheet1!A1. The next four rows should be referencing worksheet1!A2. But if I try to do this by filling down A1:A4, it references worksheet1!A5.
I need a way to quickly duplicate those four rows and have the correct references. Here's how it should be.
worksheet1!A1 -> worksheet2!A1
worksheet1!A1 -> worksheet2!A2
worksheet1!A1 -> worksheet2!A3
worksheet1!A1 -> worksheet2!A4
worksheet1!A2 -> worksheet2!A5
worksheet1!A2 -> worksheet2!A6
worksheet1!A2 -> worksheet2!A7
worksheet1!A2 -> worksheet2!A8
At the moment, this is how it's coming out. You can see that three of the sales rows are just skipped over.
worksheet1!A1 -> worksheet2!A1
worksheet1!A1 -> worksheet2!A2
worksheet1!A1 -> worksheet2!A3
worksheet1!A1 -> worksheet2!A4
worksheet1!A5 -> worksheet2!A5
worksheet1!A5 -> worksheet2!A6
worksheet1!A5 -> worksheet2!A7
worksheet1!A5 -> worksheet2!A8
There is probably a really easy and obvious way to do this, but my Excel skills are limited, and I wasn't able to find anything on Google.
Solution:
I was able to work out a way to do it. It just divides the row number by four, then rounds up to the nearest whole number.
=INDIRECT("'worksheet1'!A"&ROUNDUP(ROW()/4, 0))
# My real world version looks more like this, as my rows don't actually start on row 1
=INDIRECT("'worksheet1'!A"&ROUNDUP((ROW()-1)/4, 0) + 8)
microsoft-excel microsoft-excel-2007 spreadsheet
asked Aug 8 '12 at 13:09
user151073user151073
43
add a comment |
I have a worksheet with a list of sales, one sale per row. In order to import this data into my accounting software, I need to create a separate worksheet that formats the data differently. Each row from the first worksheet will take up four rows in the second worksheet.
I set up my four rows in the second worksheet and make all the relative references to the first row in the first worksheet. When I make a copy of the four rows the cell references are incorrect. For example, the first four rows should reference worksheet1!A1. The next four rows should be referencing worksheet1!A2. But if I try to do this by filling down A1:A4, it references worksheet1!A5.
I need a way to quickly duplicate those four rows and have the correct references. Here's how it should be.
worksheet1!A1 -> worksheet2!A1
worksheet1!A1 -> worksheet2!A2
worksheet1!A1 -> worksheet2!A3
worksheet1!A1 -> worksheet2!A4
worksheet1!A2 -> worksheet2!A5
worksheet1!A2 -> worksheet2!A6
worksheet1!A2 -> worksheet2!A7
worksheet1!A2 -> worksheet2!A8
At the moment, this is how it's coming out. You can see that three of the sales rows are just skipped over.
worksheet1!A1 -> worksheet2!A1
worksheet1!A1 -> worksheet2!A2
worksheet1!A1 -> worksheet2!A3
worksheet1!A1 -> worksheet2!A4
worksheet1!A5 -> worksheet2!A5
worksheet1!A5 -> worksheet2!A6
worksheet1!A5 -> worksheet2!A7
worksheet1!A5 -> worksheet2!A8
There is probably a really easy and obvious way to do this, but my Excel skills are limited, and I wasn't able to find anything on Google.
Solution:
I was able to work out a way to do it. It just divides the row number by four, then rounds up to the nearest whole number.
=INDIRECT("'worksheet1'!A"&ROUNDUP(ROW()/4, 0))
# My real world version looks more like this, as my rows don't actually start on row 1
=INDIRECT("'worksheet1'!A"&ROUNDUP((ROW()-1)/4, 0) + 8)
microsoft-excel microsoft-excel-2007 spreadsheet
asked Aug 8 '12 at 13:09
user151073user151073
43
Check out the answers provided for this question. One's a manual solution; the other is with VBA.
– Zairja
Aug 8 '12 at 18:21
add a comment |
I have a worksheet with a list of sales, one sale per row. In order to import this data into my accounting software, I need to create a separate worksheet that formats the data differently. Each row from the first worksheet will take up four rows in the second worksheet.
I set up my four rows in the second worksheet and make all the relative references to the first row in the first worksheet. When I make a copy of the four rows the cell references are incorrect. For example, the first four rows should reference worksheet1!A1. The next four rows should be referencing worksheet1!A2. But if I try to do this by filling down A1:A4, it references worksheet1!A5.
I need a way to quickly duplicate those four rows and have the correct references. Here's how it should be.
worksheet1!A1 -> worksheet2!A1
worksheet1!A1 -> worksheet2!A2
worksheet1!A1 -> worksheet2!A3
worksheet1!A1 -> worksheet2!A4
worksheet1!A2 -> worksheet2!A5
worksheet1!A2 -> worksheet2!A6
worksheet1!A2 -> worksheet2!A7
worksheet1!A2 -> worksheet2!A8
At the moment, this is how it's coming out. You can see that three of the sales rows are just skipped over.
worksheet1!A1 -> worksheet2!A1
worksheet1!A1 -> worksheet2!A2
worksheet1!A1 -> worksheet2!A3
worksheet1!A1 -> worksheet2!A4
worksheet1!A5 -> worksheet2!A5
worksheet1!A5 -> worksheet2!A6
worksheet1!A5 -> worksheet2!A7
worksheet1!A5 -> worksheet2!A8
There is probably a really easy and obvious way to do this, but my Excel skills are limited, and I wasn't able to find anything on Google.
Solution:
I was able to work out a way to do it. It just divides the row number by four, then rounds up to the nearest whole number.
=INDIRECT("'worksheet1'!A"&ROUNDUP(ROW()/4, 0))
# My real world version looks more like this, as my rows don't actually start on row 1
=INDIRECT("'worksheet1'!A"&ROUNDUP((ROW()-1)/4, 0) + 8)
microsoft-excel microsoft-excel-2007 spreadsheet
asked Aug 8 '12 at 13:09
user151073user151073
43
I have a worksheet with a list of sales, one sale per row. In order to import this data into my accounting software, I need to create a separate worksheet that formats the data differently. Each row from the first worksheet will take up four rows in the second worksheet.
I set up my four rows in the second worksheet and make all the relative references to the first row in the first worksheet. When I make a copy of the four rows the cell references are incorrect. For example, the first four rows should reference worksheet1!A1. The next four rows should be referencing worksheet1!A2. But if I try to do this by filling down A1:A4, it references worksheet1!A5.
I need a way to quickly duplicate those four rows and have the correct references. Here's how it should be.
worksheet1!A1 -> worksheet2!A1
worksheet1!A1 -> worksheet2!A2
worksheet1!A1 -> worksheet2!A3
worksheet1!A1 -> worksheet2!A4
worksheet1!A2 -> worksheet2!A5
worksheet1!A2 -> worksheet2!A6
worksheet1!A2 -> worksheet2!A7
worksheet1!A2 -> worksheet2!A8
At the moment, this is how it's coming out. You can see that three of the sales rows are just skipped over.
worksheet1!A1 -> worksheet2!A1
worksheet1!A1 -> worksheet2!A2
worksheet1!A1 -> worksheet2!A3
worksheet1!A1 -> worksheet2!A4
worksheet1!A5 -> worksheet2!A5
worksheet1!A5 -> worksheet2!A6
worksheet1!A5 -> worksheet2!A7
worksheet1!A5 -> worksheet2!A8
There is probably a really easy and obvious way to do this, but my Excel skills are limited, and I wasn't able to find anything on Google.
Solution:
I was able to work out a way to do it. It just divides the row number by four, then rounds up to the nearest whole number.
=INDIRECT("'worksheet1'!A"&ROUNDUP(ROW()/4, 0))
# My real world version looks more like this, as my rows don't actually start on row 1
=INDIRECT("'worksheet1'!A"&ROUNDUP((ROW()-1)/4, 0) + 8)
microsoft-excel microsoft-excel-2007 spreadsheet
microsoft-excel microsoft-excel-2007 spreadsheet
asked Aug 8 '12 at 13:09
user151073user151073
43
asked Aug 8 '12 at 13:09
user151073user151073
43
edited Aug 9 '12 at 9:03
user151073
asked Aug 8 '12 at 13:09
user151073user151073
43
asked Aug 8 '12 at 13:09
user151073user151073
43
asked Aug 8 '12 at 13:09
user151073user151073
43
43
Check out the answers provided for this question. One's a manual solution; the other is with VBA.
– Zairja
Aug 8 '12 at 18:21
add a comment |
Check out the answers provided for this question. One's a manual solution; the other is with VBA.
– Zairja
Aug 8 '12 at 18:21
Check out the answers provided for this question. One's a manual solution; the other is with VBA.
– Zairja
Aug 8 '12 at 18:21
Check out the answers provided for this question. One's a manual solution; the other is with VBA.
– Zairja
Aug 8 '12 at 18:21
add a comment |
1 Answer
1
active
oldest
votes
Assuming you're starting at A1 on the second sheet, you can fill the following formula down column A to get the desired results.
=IF(INT((ROW()+3)/4)=(ROW()+3)/4,INDIRECT("worksheet1!A"&(ROW()+3)/4),"")
It's a bit convoluted and will make your worksheet harder to audit, but it will get the job done. If you need clear, easy-to-follow formulas, VBA is probably the way to go.
answered Aug 8 '12 at 13:41
ExcellllExcellll
11.2k74164
Thanks for putting this code together, but I think you misunderstood what I'm trying to do. I think the examples I made were misleading. I've edited them to make it more clear.
– user151073
Aug 8 '12 at 21:11
I was able to make my own solution by modifying yours. Thanks :-)
– user151073
Aug 9 '12 at 8:57
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%2f459073%2freferencing-a-single-row-to-multiple-rows-in-excel%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
Assuming you're starting at A1 on the second sheet, you can fill the following formula down column A to get the desired results.
=IF(INT((ROW()+3)/4)=(ROW()+3)/4,INDIRECT("worksheet1!A"&(ROW()+3)/4),"")
It's a bit convoluted and will make your worksheet harder to audit, but it will get the job done. If you need clear, easy-to-follow formulas, VBA is probably the way to go.
answered Aug 8 '12 at 13:41
ExcellllExcellll
11.2k74164
Thanks for putting this code together, but I think you misunderstood what I'm trying to do. I think the examples I made were misleading. I've edited them to make it more clear.
– user151073
Aug 8 '12 at 21:11
I was able to make my own solution by modifying yours. Thanks :-)
– user151073
Aug 9 '12 at 8:57
add a comment |
Assuming you're starting at A1 on the second sheet, you can fill the following formula down column A to get the desired results.
=IF(INT((ROW()+3)/4)=(ROW()+3)/4,INDIRECT("worksheet1!A"&(ROW()+3)/4),"")
It's a bit convoluted and will make your worksheet harder to audit, but it will get the job done. If you need clear, easy-to-follow formulas, VBA is probably the way to go.
answered Aug 8 '12 at 13:41
ExcellllExcellll
11.2k74164
Thanks for putting this code together, but I think you misunderstood what I'm trying to do. I think the examples I made were misleading. I've edited them to make it more clear.
– user151073
Aug 8 '12 at 21:11
I was able to make my own solution by modifying yours. Thanks :-)
– user151073
Aug 9 '12 at 8:57
add a comment |
Assuming you're starting at A1 on the second sheet, you can fill the following formula down column A to get the desired results.
=IF(INT((ROW()+3)/4)=(ROW()+3)/4,INDIRECT("worksheet1!A"&(ROW()+3)/4),"")
It's a bit convoluted and will make your worksheet harder to audit, but it will get the job done. If you need clear, easy-to-follow formulas, VBA is probably the way to go.
answered Aug 8 '12 at 13:41
ExcellllExcellll
11.2k74164
Assuming you're starting at A1 on the second sheet, you can fill the following formula down column A to get the desired results.
=IF(INT((ROW()+3)/4)=(ROW()+3)/4,INDIRECT("worksheet1!A"&(ROW()+3)/4),"")
It's a bit convoluted and will make your worksheet harder to audit, but it will get the job done. If you need clear, easy-to-follow formulas, VBA is probably the way to go.
answered Aug 8 '12 at 13:41
ExcellllExcellll
11.2k74164
answered Aug 8 '12 at 13:41
ExcellllExcellll
11.2k74164
answered Aug 8 '12 at 13:41
ExcellllExcellll
11.2k74164
answered Aug 8 '12 at 13:41
ExcellllExcellll
11.2k74164
11.2k74164
Thanks for putting this code together, but I think you misunderstood what I'm trying to do. I think the examples I made were misleading. I've edited them to make it more clear.
– user151073
Aug 8 '12 at 21:11
I was able to make my own solution by modifying yours. Thanks :-)
– user151073
Aug 9 '12 at 8:57
add a comment |
Thanks for putting this code together, but I think you misunderstood what I'm trying to do. I think the examples I made were misleading. I've edited them to make it more clear.
– user151073
Aug 8 '12 at 21:11
I was able to make my own solution by modifying yours. Thanks :-)
– user151073
Aug 9 '12 at 8:57
Thanks for putting this code together, but I think you misunderstood what I'm trying to do. I think the examples I made were misleading. I've edited them to make it more clear.
– user151073
Aug 8 '12 at 21:11
Thanks for putting this code together, but I think you misunderstood what I'm trying to do. I think the examples I made were misleading. I've edited them to make it more clear.
– user151073
Aug 8 '12 at 21:11
I was able to make my own solution by modifying yours. Thanks :-)
– user151073
Aug 9 '12 at 8:57
I was able to make my own solution by modifying yours. Thanks :-)
– user151073
Aug 9 '12 at 8:57
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%2f459073%2freferencing-a-single-row-to-multiple-rows-in-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
Check out the answers provided for this question. One's a manual solution; the other is with VBA.
– Zairja
Aug 8 '12 at 18:21