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

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,722
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,722
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
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,170,946
Messages
5,872,885
Members
432,952
Latest member
CincyExcel1

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