Pick the row number from the cell value.

[motilulla]

Using Excel 2010

Hello,

I am using following array formula which is as below

{=IF(MAX(($GI6=$D$6:$D$4108)*(ROW($D$6:$D$4108)))=0,,MAX(($GI6=$D$6:$D$4108)*(ROW($D$6:$D$4108))))}

Where I am using range $D$6:$D$4108 direct in the formula but I need to pick row number from the cell A1 and A2 for example cell value A1=6 and A2=250 is it possible. So it would be range actually $D$6:$D$250

Please help

Thank you all.

Regards,
Moti

 

[Snakehips]

@motilulla You can express your variable ranges using either INDIRECT or OFFSET functions.
INDIRECT is volatile so perhaps best to use OFFSET

Below are examples as used in a simple SUM formula.

Book2
	A	B	C	D
1	6			1
2	15			2
3				3
4	105			4
5	105			5
6				6
7				7
8				8
9				9
10				10
11				11
12				12
13				13
14				14
15				15
16				16
17				17
18				18
19				19
20				20
21				21
22				22
23				23
24				24
25				25
26				26
27				27
Sheet3

Cell Formulas
Range	Formula
A4	A4	=SUM(INDIRECT("$D$"&$A$1&":$D$"&$A$2))
A5	A5	=SUM(OFFSET($D$1,$A$1-1,,$A$2-$A$1+1))

Hope that helps.

 

[motilulla]

@motilulla You can express your variable ranges using either INDIRECT or OFFSET functions.
INDIRECT is volatile so perhaps best to use OFFSET

Below are examples as used in a simple SUM formula.

Book2
	A	B	C	D
1	6			1
2	15			2
3				3
4	105			4
5	105			5
6				6
7				7
8				8
9				9
10				10
11				11
12				12
13				13
14				14
15				15
16				16
17				17
18				18
19				19
20				20
21				21
22				22
23				23
24				24
25				25
26				26
27				27
Sheet3

Cell Formulas
Range	Formula
A4	A4	=SUM(INDIRECT("$D$"&$A$1&":$D$"&$A$2))
A5	A5	=SUM(OFFSET($D$1,$A$1-1,,$A$2-$A$1+1))

Hope that helps.

Snakehips, when I applied INDIRECT or OFFSET functions with my formula it seems both functions are volatile as I find INDIRECT bit easier, so I used the INDIRECT. It results ideal!

Thank you so much for your help

Good Luck

Kind Regards,
Moti

 

[Snakehips]

@motilulla Pleased to have helped.
I should have realised that Indirect was also volatile.

You can use INDEX which is not. eg

=SUM(INDEX(D:D,$A$1,):INDEX(D:D,$A$2,))

 

[motilulla]

@motilulla Pleased to have helped.
I should have realised that Indirect was also volatile.

You can use INDEX which is not. eg

=SUM(INDEX(D:D,$A$1,):INDEX(D:D,$A$2,))

Snakehips, thank you for giving an option of non-volatile function this worked fine.

Have a great weekend, Good Luck

Kind Regards,
Moti