Excel – Referencing a single row to multiple rows in Excel

microsoft excelmicrosoft-excel-2007spreadsheet

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.

Best 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.

Related Question