flippertie
New Member
- Joined
- Mar 29, 2013
- Messages
- 15
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!
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!