OFFSET

hhenrion

Board Regular
Joined
Dec 19, 2005
Messages
73
Hi experts,

I want to select a range of size 3 x 4, located 5 lines and 1 row off the reference cell (let's say cell G10).

How do I write that using OFFSET keyword ?

Thanks a lot,
regards,

H.
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
size 3 x 4 equals what exactly ... 3 rows x 4 columns, or 3 columns x 4 rows?

And location ... 5 lines and 1 row means what? A line is a row, isn't it. Which of these refers to columns?

The format is:
=OFFSET(refcell,rows_to_offset,cols_to_offset,return_size_in_rows,return_size_in_cols)
which is in the help actually.
 
Upvote 0
Something like:

OFFSET(G10,5,1,3,4)

but you need to specify in which direction you are counting rows and columns and you need to know if the matrix is 3 high or 4 high by 3 wide or 4 wide.

Also, this function has to be used within another to work..or you will get a #Value error..


e.g. =Sum(OFFSET(G10,5,1,3,4)) will sum the values in your matrix.
 
Upvote 0
Just realised that this query may be about VBA ... "select a range" ... "OFFSET keyword" .... is that the case?
 
Upvote 0
Glenn,

actually... YES (sorry I was unclear, this is the end opf the day...).

I am in VBA trying to select and area of i rows and j columns, and the top left cell of the area is ii rows and jj columns of the reference cell.

Thanks in advance,

H.
 
Upvote 0
Use Offset(rows,cols).Resize(numrows,numcols)
 
Upvote 0

Forum statistics

Threads
1,214,525
Messages
6,120,052
Members
448,940
Latest member
mdusw

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
Back
Top