Best way to use an OFFSET formula over a range of cells?

[flippertie]

I have a table that is 10 columns wide. On a separate worksheet I want to display the last 10 rows of the table - 100 cells in all.

Getting the value of one of the cells is easy enough - I used:

=OFFSET(DataTableFirstCell,COUNTA(Sheet2!$A:$A)-1,0)

If I copy/paste, or drag the fill handle to expand the target range I end up with the same formula and same value in all 100 target cells. I know I can edit each cell to modify the offset -1,-1, then -1,-2 then -1,-3 etc but that's mind-numbingly tedious and likely to introduce error.

There must be a way to have a cell reference another, then copy the formula to adjacent cells and and have the result show the content of cells adjacent to the original source.

I've been trying INDIRECT and INDEX with ROW and COLUMN - but I can't work it out. Any suggestions or exam[ples wouldbe appreciated!

 

[Aladin Akyurek]

Thanks! I didn't realise I could address the properties of the table like that. I shall explore! And i like that your solution allows me to pick the number of rows required. I shall remember that for future use

You are welcome. Note that the set up is also fast (less costly).