Calculate cell number in the formula

I want to reference a cell. Sheet2!D1 and the next row will have Sheet2!D10. Instead of writing this I want to write Sheet2!DRow()+8.

What's the correct syntax of calculating row()+8 and then appending it to D?

Appreciate any tips.

Solution 1:

The solution suggested by @MohammadYusuf will work.

However INDIRECT is a volatile function which means it will recalculate any time anything on the worksheet recalculates and may slow things down.

A non-volatile equivalent can be constructed using INDEX:

=INDEX(Sheet2!$D:$D,IF(ROWS($1:1)=1,1,(ROWS($1:1)-1)*10),1)

As you fill down, the ROWS($1:1) argument will increment and produce your series of row numbers.

An alternative, if you have Office 365, would be:

    =LET(x,INDEX(Sheet2!$D:$D,SEQUENCE(100,,1,10),1),
        IF( x="","",x))

entered into a single cell and it will Spill down the results

Hmm... (LibreOffice uses . instead of ! apparently)

=OFFSET(Sheet2.D1,0,0)
=OFFSET(Sheet2.D1,8,0)

... is another option.

The second argument (i.e. 0 and 8) may be replaced by (ROW()-n)*8 for indexing depending on where the formula resides, n=1 for first row.