Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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)
share|improve this question
    
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 comment

1 Answer

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.

share|improve this answer
    
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 comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.