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

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,547
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.
 

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
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.
 

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,547
Just realised that this query may be about VBA ... "select a range" ... "OFFSET keyword" .... is that the case?
 

hhenrion

Board Regular
Joined
Dec 19, 2005
Messages
73

ADVERTISEMENT

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.
 

Forum statistics

Threads
1,136,519
Messages
5,676,333
Members
419,619
Latest member
jalme

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