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

#### flippertie

##### New Member
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!

### Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
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

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?

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.

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?

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

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?

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...

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

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:
Rich (BB code):
``=ROWS(DataTable)-MIN(5,ROWS(DataTable))+1``

A3, just enter, copy across, and down:
Rich (BB code):
``=IFERROR(INDEX(DataTable,ROWS(A\$3:A3)+\$D\$1-1,0),"")``

Last edited:
Code:
``=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

Replies
1
Views
253
Replies
26
Views
780
Replies
8
Views
261
Replies
8
Views
155
Replies
3
Views
181

1,217,382
Messages
6,136,239
Members
450,000
Latest member
jgp19

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back