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

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!
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

ArthriticPanda

Active Member
Joined
Feb 24, 2013
Messages
292
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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

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

New Member
Joined
Mar 29, 2013
Messages
15

ADVERTISEMENT

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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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

New Member
Joined
Mar 29, 2013
Messages
15

ADVERTISEMENT

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

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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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-1Field-2Field-3Field-4Field-5
4nadvadkadlad
70.29353490.97298920.45449750.8766133
20.764953610.65760190.943667970.8160031
90.093490080.81317920.746715160.170588
80.510085390.29514480.048716360.7111763
40.077934970.71984580.589279450.4885964
60.17936480.88974840.387077010.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:5Start row:3
Field-1Field-2Field-3Field-4Field-5
20.764953610.6576018870.9436680.8160031
90.093490080.8131791880.74671520.170588
80.510085390.2951448280.04871640.7111763
40.077934970.7198457660.58927950.4885964
60.17936480.8897484210.3870770.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:

flippertie

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

Watch MrExcel Video

Forum statistics

Threads
1,133,532
Messages
5,659,368
Members
418,499
Latest member
mbcmel

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top