0

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.

fixer1234
  • 27,486
user151073
  • 9

2 Answers2

1

Solution moved on behalf of the OP from within the question to an answer.

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)
fixer1234
  • 27,486
0

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.

Excellll
  • 12,717
  • 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 08 '12 at 21:11
  • I was able to make my own solution by modifying yours. Thanks :-) – user151073 Aug 09 '12 at 08:57