OOO: Increment sheets for fill down

Question

I have a row of cells on "sheet1" referencing a set of cells on a different sheet. I want to fill down identical sets of cells while incrementing only the sheet.

Another way to describe the problem: I have a master table of row records referencing other sheets which are "forms". How can I copy a set of cells on a "form" sheet to a row of cells on a master sheet?

Answer 1

If you fill in two rows just how you want them you will probably be able to select the block you want to replicate and use the fill handle to drag them down. Autofill is quite clever when you select two rows and drag. It will often work out what you have changed and just make that a series.

If that does not work you could insert the sheet names in a column and then use INDIRECT references for the other formulas.

Answer 2

[Copying from Excel: portable formula to reference data on relative worksheet.]

Create the following macro:

Function SHEET_OFFSET(Offset, Ref)
'   Returns cell contents at Ref, in sheet Offset
    Application.Volatile
    With Application.Caller.Parent
        SHEET_OFFSET = .Parent.Sheets(.Index + Offset) _
         .Range(Ref.Address).Value
    End With
End Function

See How do I add VBA in MS Office? for general information on using macros.

Then, if you want the references on Sheet1 to look like this:

         A             B             C
1   =Sheet2!G17   =Sheet2!M42   =Sheet2!Q95   ...
2   =Sheet3!G17   =Sheet3!M42   =Sheet3!Q95   ...
         ︙            ︙            ︙

set A1 to SHEET_OFFSET(ROW(), $G$17), etc.   The first argument is the sheet number, relative to the current one, and the second argument is the cell reference.