Duplicated cells skip 10 rows

Question

I have an Excel spreadsheet with two sheets, Sheet 1 contains some text and formulas I wish to duplicate down to write 2,000 odd lines of C# code for my project. Extremely repetitive, so I thought I could use Excel to write it for me. Sheet 2 contains an extract from my database which I wish to use to populate that values with. My section of Excel code looks like this and is spread out over 10 rows and 5 columns:

new AccountingPeriod()              
    {           
        MonthCovered="=Sheet2!B2",
        StartDate=DateTime.Parse("Sheet2!C2"),
        EndDate=DateTime.Parse("Sheet2!D2"),
        AccountingPeriodDescription="Sheet2!E2",
        Active='=Sheet2!F2',
        April='=Sheet2!G2,
        TaxYear="Sheet2!H2"
    },      

When I highlight these 10 rows and use the Excel duplicate tool I want the cell references (for example, in my spreadsheet Sheet1!D3 = Sheet2!B2, I want Sheet1!D13 = Sheet2!B3 NOT Sheet2!B13) to increment by 1, not 10 as it's doing in my spreadsheet.

We have tried an alternative solution of writing a macro to insert 10 blank rows in Sheet2 for every populated row so that the duplicated references are correct in Sheet1 but we're currently failing miserably at getting that to work correctly.

Answer 1

Instead of a direct reference that is going to change on a 1-to-1 with the copy, use the INDEX function with a little maths to achieve the 10-to-1 row stagger.

In Sheet1!D3 this references Sheet2!B2.

=INDEX(Sheet2!B:B,INT(ROW(1:1)/10)+2)

In Sheet1!D13 this references Sheet2!B3.

The OFFSET function can accomplish the same thing but it is a volatile function that recalculates whenever anything in the workbook changes. INDEX provides the same functionality while remaining non-volatile and will only recalculate when something that affects its returned value changes.