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!

 

[ArthriticPanda]

Hi flippertie,

Just looking at your sample formula there, you have the cell ref to absolute ($ signs on A) which means it will always ref that cell no matter where you drag it. Try removing them and try dragging again.

AP

 

[Aladin Akyurek]

What is the current range? What is the sheet name? What is the range of the first row? Does it house column headings? Is there a range that is numeric?

 

[flippertie]

you have the cell ref to absolute ($ signs on A) which means it will always ref that cell
AP

Thanks for replying. I tried removing the $ symbols - Dragging still returns the same formula and same result in all cells.

That section of the formula - COUNTA(Sheet2!$A:$A) - returns the number of filled cells in the datatable for the which hould remain constant. What i'm looking for is a way to change the last bit -1,0 etc so I get the different values returned.

 

[flippertie]

What is the current range? What is the sheet name? What is the range of the first row? Does it house column headings? Is there a range that is numeric?

Thanks for replying. I'm not sure I understand all your questions.

The OFFSET formula could be placed anywhere in the spreadsheet - though it will be in Cells C3 - L13 of Sheet1.
I have not yet named this range, though LastTenReadings would be appropriate.

The data table is in Sheet2 as shown in my original formula. The table is called 'DataTable' and expands as new lines are added to the bottom of it. The range "DataTableFirstCell" is the top left cell of DataTable (Sheet2!A1) and yes it is a header row. The data in each row is a mix of numeric and date / time values

 

[Aladin Akyurek]

Thanks for replying. I'm not sure I understand all your questions.

The OFFSET formula could be placed anywhere in the spreadsheet - though it will be in Cells C3 - L13 of Sheet1.
I have not yet named this range, though LastTenReadings would be appropriate.

The data table is in Sheet2 as shown in my original formula. The table is called 'DataTable' and expands as new lines are added to the bottom of it. The range "DataTableFirstCell" is the top left cell of DataTable (Sheet2!A1) and yes it is a header row. The data in each row is a mix of numeric and date / time values

Is DataTable an Excel Table created by means of the Insert | Table option? If it's not, what is the current range and which column is numeric, e.e., date, time, or other numeric figures?

 

[flippertie]

If anyone's interested I solved it
I named the top left cell of the place where I wanted to display the results as 'ResultsFirstCell', then modified my original formula to this:

=OFFSET(DataTableFirstCell,COUNTA(BPDataTable!$A:$A)-(ROW()-ROW(ResultsFirstCell))-1,(COLUMN()-COLUMN(ResultsFirstCell)+1))

This counts the number of rows/columns between the current cell and ResultsFirstCell, and displays the contents of the cell the same distance from the Cell at the bottom of the first column of the DataTable.

It also has the unexpected benefit of displaying the rows in reverse order (most recent at the top) which makes a lot of sense.

PS While messing around with this i realised a much easier solution would have been a macro to copy the last 10 rows of the DataTable and paste them at the results location. But then I wouldn't have rediscovered the 'pleasure' of playing with Excel formulas...

 

[flippertie]

Yes - DataTable is an Excel table. The first few columns are numeric values and the last two a date and time stamp. But as I posted below - I' have a working solution - a formula i can copy across multiple cells and have it return values from the table. If there's a better way to do it I'd be happy to learn - but at least I have what i need working

 

[Aladin Akyurek]

Yes - DataTable is an Excel table. The first few columns are numeric values and the last two a date and time stamp. But as I posted below - I' have a working solution - a formula i can copy across multiple cells and have it return values from the table. If there's a better way to do it I'd be happy to learn - but at least I have what i need working

Let Sheet1, A:E, houses DataTable, created by means of the Insert | Table option.

Field-1	Field-2	Field-3	Field-4	Field-5
4	nad	vad	kad	lad
7	0.2935349	0.9729892	0.4544975	0.8766133
2	0.76495361	0.6576019	0.94366797	0.8160031
9	0.09349008	0.8131792	0.74671516	0.170588
8	0.51008539	0.2951448	0.04871636	0.7111763
4	0.07793497	0.7198458	0.58927945	0.4885964
6	0.1793648	0.8897484	0.38707701	0.4616286

<colgroup><col style="width: 65pt; mso-width-source: userset; mso-width-alt: 3100;" width="87"> <col style="width: 62pt; mso-width-source: userset; mso-width-alt: 2929;" width="82"> <col style="width: 58pt; mso-width-source: userset; mso-width-alt: 2730;" width="77"> <col style="width: 59pt; mso-width-source: userset; mso-width-alt: 2816;" width="79"> <col style="width: 55pt; mso-width-source: userset; mso-width-alt: 2588;" width="73"> <tbody>
</tbody>

Sheet2, A:E, the processing...

N:	5	Start row:	3
Field-1	Field-2	Field-3	Field-4	Field-5
2	0.76495361	0.657601887	0.943668	0.8160031
9	0.09349008	0.813179188	0.7467152	0.170588
8	0.51008539	0.295144828	0.0487164	0.7111763
4	0.07793497	0.719845766	0.5892795	0.4885964
6	0.1793648	0.889748421	0.387077	0.4616286

<colgroup><col style="width: 60pt; mso-width-source: userset; mso-width-alt: 2844;" span="2" width="80"> <col style="width: 70pt; mso-width-source: userset; mso-width-alt: 3299;" width="93"> <col style="width: 56pt; mso-width-source: userset; mso-width-alt: 2645;" width="74"> <col style="width: 57pt; mso-width-source: userset; mso-width-alt: 2702;" width="76"> <tbody>
</tbody>

Let's say that we want the last 5 rows of the table:

B1: 5 (Last N rows)

D1, just enter:

=ROWS(DataTable)-MIN(5,ROWS(DataTable))+1

A3, just enter, copy across, and down:

=IFERROR(INDEX(DataTable,ROWS(A$3:A3)+$D$1-1,0),"")

 

[flippertie]

=ROWS(DataTable)-MIN(5,ROWS(DataTable))+1

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