Dear all; I need to filter my data in that cell under data (one cell)
to twelve cell like as results cells

see my
example again

data results
261.0 261.0 231.7 277.3 308.3 306.0 322.0 204.5 263.0 302.9 237.4 249.3 219.3
231.7 214.6 240.7 246.8 206.5 253.0 257.9 274.0 259.0 260.2 258.9 310.3 267.4
277.3 264.4 254.6 329.2 296.0 341.3 250.4 319.8 221.6 286.2 297.1 327.6 267.5
308.3
306.0
322.0
204.5
263.0
302.9
237.4
249.3
219.3
214.6
240.7
246.8
206.5
253.0
257.9
274.0
259.0
260.2
258.9
310.3
267.4
264.4
254.6
329.2
296.0
341.3
250.4
319.8
221.6
286.2
297.1
327.6
267.5

Hi,

Try

=OFFSET(INDIRECT("A"&CHOOSE(ROW(A2),0,2,11,20,29)) ,COLUMN(A:A)-1,)

VBA Noob


Ahmad wrote:
Dear all; I need to filter my data in that cell under data (one cell)
to twelve cell like as results cells

see my
example again

data results
261.0 261.0 231.7 277.3 308.3 306.0 322.0 204.5 263.0 302.9 237.4 249.3 219.3
231.7 214.6 240.7 246.8 206.5 253.0 257.9 274.0 259.0 260.2 258.9 310.3 267.4
277.3 264.4 254.6 329.2 296.0 341.3 250.4 319.8 221.6 286.2 297.1 327.6 267.5
308.3
306.0
322.0
204.5
263.0
302.9
237.4
249.3
219.3
214.6
240.7
246.8
206.5
253.0
257.9
274.0
259.0
260.2
258.9
310.3
267.4
264.4
254.6
329.2
296.0
341.3
250.4
319.8
221.6
286.2
297.1
327.6
267.5

Dear VBA Noob;
I have try your function there are some wrongs in the second and theerd
rows, while the first row is ok
VBA Noob yazdi:
Hi,

Try

=OFFSET(INDIRECT("A"&CHOOSE(ROW(A2),0,2,11,20,29)) ,COLUMN(A:A)-1,)

VBA Noob


Ahmad wrote:
Dear all; I need to filter my data in that cell under data (one cell)
to twelve cell like as results cells

see my
example again

data results
261.0 261.0 231.7 277.3 308.3 306.0 322.0 204.5 263.0 302.9 237.4 249.3 219.3
231.7 214.6 240.7 246.8 206.5 253.0 257.9 274.0 259.0 260.2 258.9 310.3 267.4
277.3 264.4 254.6 329.2 296.0 341.3 250.4 319.8 221.6 286.2 297.1 327.6 267.5
308.3
306.0
322.0
204.5
263.0
302.9
237.4
249.3
219.3
214.6
240.7
246.8
206.5
253.0
257.9
274.0
259.0
260.2
258.9
310.3
267.4
264.4
254.6
329.2
296.0
341.3
250.4
319.8
221.6
286.2
297.1
327.6
267.5

Hi,

It assumes the data is a 9 x 4 Table and data starts in A1

CHOOSE(ROW(A2),0,2,11,20,29))

When you drag down Row(A2) changes to Row(A3) which is 3. This is then
used in the choose function to return 11 and the indirect changes it to
A11

VBA Noob

Ahmad wrote:
Dear VBA Noob;
I have try your function there are some wrongs in the second and theerd
rows, while the first row is ok
VBA Noob yazdi:
Hi,

Try

=OFFSET(INDIRECT("A"&CHOOSE(ROW(A2),0,2,11,20,29)) ,COLUMN(A:A)-1,)

VBA Noob


Ahmad wrote:
Dear all; I need to filter my data in that cell under data (one cell)
to twelve cell like as results cells

see my
example again

data results
261.0 261.0 231.7 277.3 308.3 306.0 322.0 204.5 263.0 302.9 237.4 249.3 219.3
231.7 214.6 240.7 246.8 206.5 253.0 257.9 274.0 259.0 260.2 258.9 310.3 267.4
277.3 264.4 254.6 329.2 296.0 341.3 250.4 319.8 221.6 286.2 297.1 327.6 267.5
308.3
306.0
322.0
204.5
263.0
302.9
237.4
249.3
219.3
214.6
240.7
246.8
206.5
253.0
257.9
274.0
259.0
260.2
258.9
310.3
267.4
264.4
254.6
329.2
296.0
341.3
250.4
319.8
221.6
286.2
297.1
327.6
267.5

Hi
my data is a 12x3 table,
VBA Noob yazdi:
Hi,

It assumes the data is a 9 x 4 Table and data starts in A1

CHOOSE(ROW(A2),0,2,11,20,29))

When you drag down Row(A2) changes to Row(A3) which is 3. This is then
used in the choose function to return 11 and the indirect changes it to
A11

VBA Noob

Ahmad wrote:
Dear VBA Noob;
I have try your function there are some wrongs in the second and theerd
rows, while the first row is ok
VBA Noob yazdi:
Hi,

Try

=OFFSET(INDIRECT("A"&CHOOSE(ROW(A2),0,2,11,20,29)) ,COLUMN(A:A)-1,)

VBA Noob


Ahmad wrote:
Dear all; I need to filter my data in that cell under data (one cell)
to twelve cell like as results cells

see my
example again

data results
261.0 261.0 231.7 277.3 308.3 306.0 322.0 204.5 263.0 302.9 237.4 249.3 219.3
231.7 214.6 240.7 246.8 206.5 253.0 257.9 274.0 259.0 260.2 258.9 310.3 267.4
277.3 264.4 254.6 329.2 296.0 341.3 250.4 319.8 221.6 286.2 297.1 327.6 267.5
308.3
306.0
322.0
204.5
263.0
302.9
237.4
249.3
219.3
214.6
240.7
246.8
206.5
253.0
257.9
274.0
259.0
260.2
258.9
310.3
267.4
264.4
254.6
329.2
296.0
341.3
250.4
319.8
221.6
286.2
297.1
327.6
267.5

Doh,

Try

=OFFSET(INDIRECT("A"&CHOOSE(ROW(A2),0,2,14,26)),CO LUMN(A:A)-1,)

VBA Noob


Ahmad wrote:
Hi
my data is a 12x3 table,
VBA Noob yazdi:
Hi,

It assumes the data is a 9 x 4 Table and data starts in A1

CHOOSE(ROW(A2),0,2,11,20,29))

When you drag down Row(A2) changes to Row(A3) which is 3. This is then
used in the choose function to return 11 and the indirect changes it to
A11

VBA Noob

Ahmad wrote:
Dear VBA Noob;
I have try your function there are some wrongs in the second and theerd
rows, while the first row is ok
VBA Noob yazdi:
Hi,

Try

=OFFSET(INDIRECT("A"&CHOOSE(ROW(A2),0,2,11,20,29)) ,COLUMN(A:A)-1,)

VBA Noob


Ahmad wrote:
Dear all; I need to filter my data in that cell under data (one cell)
to twelve cell like as results cells

see my
example again

data results
261.0 261.0 231.7 277.3 308.3 306.0 322.0 204.5 263.0 302.9 237.4 249.3 219.3
231.7 214.6 240.7 246.8 206.5 253.0 257.9 274.0 259.0 260.2 258.9 310.3 267.4
277.3 264.4 254.6 329.2 296.0 341.3 250.4 319.8 221.6 286.2 297.1 327.6 267.5
308.3
306.0
322.0
204.5
263.0
302.9
237.4
249.3
219.3
214.6
240.7
246.8
206.5
253.0
257.9
274.0
259.0
260.2
258.9
310.3
267.4
264.4
254.6
329.2
296.0
341.3
250.4
319.8
221.6
286.2
297.1
327.6
267.5

Dear VBA Noob;
Thank you very much, the last function work okey.

good luck

Ahmad

VBA Noob yazdi:
Doh,

Try

=OFFSET(INDIRECT("A"&CHOOSE(ROW(A2),0,2,14,26)),CO LUMN(A:A)-1,)

VBA Noob


Ahmad wrote:
Hi
my data is a 12x3 table,
VBA Noob yazdi:
Hi,

It assumes the data is a 9 x 4 Table and data starts in A1

CHOOSE(ROW(A2),0,2,11,20,29))

When you drag down Row(A2) changes to Row(A3) which is 3. This is then
used in the choose function to return 11 and the indirect changes it to
A11

VBA Noob

Ahmad wrote:
Dear VBA Noob;
I have try your function there are some wrongs in the second and theerd
rows, while the first row is ok
VBA Noob yazdi:
Hi,

Try

=OFFSET(INDIRECT("A"&CHOOSE(ROW(A2),0,2,11,20,29)) ,COLUMN(A:A)-1,)

VBA Noob


Ahmad wrote:
Dear all; I need to filter my data in that cell under data (one cell)
to twelve cell like as results cells

see my
example again

data results
261.0 261.0 231.7 277.3 308.3 306.0 322.0 204.5 263.0 302.9 237.4 249.3 219.3
231.7 214.6 240.7 246.8 206.5 253.0 257.9 274.0 259.0 260.2 258.9 310.3 267.4
277.3 264.4 254.6 329.2 296.0 341.3 250.4 319.8 221.6 286.2 297.1 327.6 267.5
308.3
306.0
322.0
204.5
263.0
302.9
237.4
249.3
219.3
214.6
240.7
246.8
206.5
253.0
257.9
274.0
259.0
260.2
258.9
310.3
267.4
264.4
254.6
329.2
296.0
341.3
250.4
319.8
221.6
286.2
297.1
327.6
267.5

Hi,

This should work also and isn't limited with the choose function

=OFFSET(INDIRECT("A"&(ROW(A1)-1)*12+2),COLUMN(A:A)-1,)

VBA Noob


Ahmad wrote:
Dear VBA Noob;
Thank you very much, the last function work okey.

good luck

Ahmad

VBA Noob yazdi:
Doh,

Try

=OFFSET(INDIRECT("A"&CHOOSE(ROW(A2),0,2,14,26)),CO LUMN(A:A)-1,)

VBA Noob


Ahmad wrote:
Hi
my data is a 12x3 table,
VBA Noob yazdi:
Hi,

It assumes the data is a 9 x 4 Table and data starts in A1

CHOOSE(ROW(A2),0,2,11,20,29))

When you drag down Row(A2) changes to Row(A3) which is 3. This is then
used in the choose function to return 11 and the indirect changes it to
A11

VBA Noob

Ahmad wrote:
Dear VBA Noob;
I have try your function there are some wrongs in the second and theerd
rows, while the first row is ok
VBA Noob yazdi:
Hi,

Try

=OFFSET(INDIRECT("A"&CHOOSE(ROW(A2),0,2,11,20,29)) ,COLUMN(A:A)-1,)

VBA Noob


Ahmad wrote:
Dear all; I need to filter my data in that cell under data (one cell)
to twelve cell like as results cells

see my
example again

data results
261.0 261.0 231.7 277.3 308.3 306.0 322.0 204.5 263.0 302.9 237.4 249.3 219.3
231.7 214.6 240.7 246.8 206.5 253.0 257.9 274.0 259.0 260.2 258.9 310.3 267.4
277.3 264.4 254.6 329.2 296.0 341.3 250.4 319.8 221.6 286.2 297.1 327.6 267.5
308.3
306.0
322.0
204.5
263.0
302.9
237.4
249.3
219.3
214.6
240.7
246.8
206.5
253.0
257.9
274.0
259.0
260.2
258.9
310.3
267.4
264.4
254.6
329.2
296.0
341.3
250.4
319.8
221.6
286.2
297.1
327.6
267.5

Dear VBA Noob
the last function works clearly, can you explain the numbers in the
function please gave me idea about this function because i am not
expert in these functions, and how i can learn this function, becuase
my data need these function, also i send you link have question like
this,
http://groups.google.com.tr/group/mi...312e157e0a20ac

VBA Noob yazdi:
Hi,

This should work also and isn't limited with the choose function

=OFFSET(INDIRECT("A"&(ROW(A1)-1)*12+2),COLUMN(A:A)-1,)

VBA Noob


Ahmad wrote:
Dear VBA Noob;
Thank you very much, the last function work okey.

good luck

Ahmad

VBA Noob yazdi:
Doh,

Try

=OFFSET(INDIRECT("A"&CHOOSE(ROW(A2),0,2,14,26)),CO LUMN(A:A)-1,)

VBA Noob


Ahmad wrote:
Hi
my data is a 12x3 table,
VBA Noob yazdi:
Hi,

It assumes the data is a 9 x 4 Table and data starts in A1

CHOOSE(ROW(A2),0,2,11,20,29))

When you drag down Row(A2) changes to Row(A3) which is 3. This is then
used in the choose function to return 11 and the indirect changes it to
A11

VBA Noob

Ahmad wrote:
Dear VBA Noob;
I have try your function there are some wrongs in the second and theerd
rows, while the first row is ok
VBA Noob yazdi:
Hi,

Try

=OFFSET(INDIRECT("A"&CHOOSE(ROW(A2),0,2,11,20,29)) ,COLUMN(A:A)-1,)

VBA Noob


Ahmad wrote:
Dear all; I need to filter my data in that cell under data (one cell)
to twelve cell like as results cells

see my
example again

data results
261.0 261.0 231.7 277.3 308.3 306.0 322.0 204.5 263.0 302.9 237.4 249.3 219.3
231.7 214.6 240.7 246.8 206.5 253.0 257.9 274.0 259.0 260.2 258.9 310.3 267.4
277.3 264.4 254.6 329.2 296.0 341.3 250.4 319.8 221.6 286.2 297.1 327.6 267.5
308.3
306.0
322.0
204.5
263.0
302.9
237.4
249.3
219.3
214.6
240.7
246.8
206.5
253.0
257.9
274.0
259.0
260.2
258.9
310.3
267.4
264.4
254.6
329.2
296.0
341.3
250.4
319.8
221.6
286.2
297.1
327.6
267.5

Hi,

Try using excel help to learn more about Offset and Indirect functions

Now if you enter =(ROW(A1)-1)*12+2 in any cell and drag down it will
return 2,14,26 etc. To break it out Row(A1) = 1 and when you drag down
Row(A2) = 2 etc.

So Row 1 would read (1-1)*12+2 which equals 2 e.g 0*12=0+2. The 12 is
needed for your 12 x 3 matrix as the indirect function looks up
A2,A14,A26 etc.

The column(A:A)-1 always returns 0

=OFFSET(INDIRECT("A"&(ROW(A1)-1)*12+2),COLUMN(A:A)-1,)

Hope that helps

VBA Noob


Ahmad wrote:
Dear VBA Noob
the last function works clearly, can you explain the numbers in the
function please gave me idea about this function because i am not
expert in these functions, and how i can learn this function, becuase
my data need these function, also i send you link have question like
this,
http://groups.google.com.tr/group/mi...312e157e0a20ac

VBA Noob yazdi:
Hi,

This should work also and isn't limited with the choose function

=OFFSET(INDIRECT("A"&(ROW(A1)-1)*12+2),COLUMN(A:A)-1,)

VBA Noob


Ahmad wrote:
Dear VBA Noob;
Thank you very much, the last function work okey.

good luck

Ahmad

VBA Noob yazdi:
Doh,

Try

=OFFSET(INDIRECT("A"&CHOOSE(ROW(A2),0,2,14,26)),CO LUMN(A:A)-1,)

VBA Noob


Ahmad wrote:
Hi
my data is a 12x3 table,
VBA Noob yazdi:
Hi,

It assumes the data is a 9 x 4 Table and data starts in A1

CHOOSE(ROW(A2),0,2,11,20,29))

When you drag down Row(A2) changes to Row(A3) which is 3. This is then
used in the choose function to return 11 and the indirect changes it to
A11

VBA Noob

Ahmad wrote:
Dear VBA Noob;
I have try your function there are some wrongs in the second and theerd
rows, while the first row is ok
VBA Noob yazdi:
Hi,

Try

=OFFSET(INDIRECT("A"&CHOOSE(ROW(A2),0,2,11,20,29)) ,COLUMN(A:A)-1,)

VBA Noob


Ahmad wrote:
Dear all; I need to filter my data in that cell under data (one cell)
to twelve cell like as results cells

see my
example again

data results
261.0 261.0 231.7 277.3 308.3 306.0 322.0 204.5 263.0 302.9 237.4 249.3 219.3
231.7 214.6 240.7 246.8 206.5 253.0 257.9 274.0 259.0 260.2 258.9 310.3 267.4
277.3 264.4 254.6 329.2 296.0 341.3 250.4 319.8 221.6 286.2 297.1 327.6 267.5
308.3
306.0
322.0
204.5
263.0
302.9
237.4
249.3
219.3
214.6
240.7
246.8
206.5
253.0
257.9
274.0
259.0
260.2
258.9
310.3
267.4
264.4
254.6
329.2
296.0
341.3
250.4
319.8
221.6
286.2
297.1
327.6
267.5

Hi
Again thank you for help, now i have understand this function

Best regards

Ahmad
VBA Noob yazdi:
Hi,

Try using excel help to learn more about Offset and Indirect functions

Now if you enter =(ROW(A1)-1)*12+2 in any cell and drag down it will
return 2,14,26 etc. To break it out Row(A1) = 1 and when you drag down
Row(A2) = 2 etc.

So Row 1 would read (1-1)*12+2 which equals 2 e.g 0*12=0+2. The 12 is
needed for your 12 x 3 matrix as the indirect function looks up
A2,A14,A26 etc.

The column(A:A)-1 always returns 0

=OFFSET(INDIRECT("A"&(ROW(A1)-1)*12+2),COLUMN(A:A)-1,)

Hope that helps

VBA Noob


Ahmad wrote:
Dear VBA Noob
the last function works clearly, can you explain the numbers in the
function please gave me idea about this function because i am not
expert in these functions, and how i can learn this function, becuase
my data need these function, also i send you link have question like
this,
http://groups.google.com.tr/group/mi...312e157e0a20ac

VBA Noob yazdi:
Hi,

This should work also and isn't limited with the choose function

=OFFSET(INDIRECT("A"&(ROW(A1)-1)*12+2),COLUMN(A:A)-1,)

VBA Noob


Ahmad wrote:
Dear VBA Noob;
Thank you very much, the last function work okey.

good luck

Ahmad

VBA Noob yazdi:
Doh,

Try

=OFFSET(INDIRECT("A"&CHOOSE(ROW(A2),0,2,14,26)),CO LUMN(A:A)-1,)

VBA Noob


Ahmad wrote:
Hi
my data is a 12x3 table,
VBA Noob yazdi:
Hi,

It assumes the data is a 9 x 4 Table and data starts in A1

CHOOSE(ROW(A2),0,2,11,20,29))

When you drag down Row(A2) changes to Row(A3) which is 3. This is then
used in the choose function to return 11 and the indirect changes it to
A11

VBA Noob

Ahmad wrote:
Dear VBA Noob;
I have try your function there are some wrongs in the second and theerd
rows, while the first row is ok
VBA Noob yazdi:
Hi,

Try

=OFFSET(INDIRECT("A"&CHOOSE(ROW(A2),0,2,11,20,29)) ,COLUMN(A:A)-1,)

VBA Noob


Ahmad wrote:
Dear all; I need to filter my data in that cell under data (one cell)
to twelve cell like as results cells

see my
example again

data results
261.0 261.0 231.7 277.3 308.3 306.0 322.0 204.5 263.0 302.9 237.4 249.3 219.3
231.7 214.6 240.7 246.8 206.5 253.0 257.9 274.0 259.0 260.2 258.9 310.3 267.4
277.3 264.4 254.6 329.2 296.0 341.3 250.4 319.8 221.6 286.2 297.1 327.6 267.5
308.3
306.0
322.0
204.5
263.0
302.9
237.4
249.3
219.3
214.6
240.7
246.8
206.5
253.0
257.9
274.0
259.0
260.2
258.9
310.3
267.4
264.4
254.6
329.2
296.0
341.3
250.4
319.8
221.6
286.2
297.1
327.6
267.5