How to change row number in function based on a value change

[psg2002]

Hi,

I'm trying to calculate the difference between 2 ranges (each range is in a different tab). I have a table that generates values and I want my function to be able to change the row value based on the return value in the table.

For example, on cell C5, I have the formula '=AVERAGE(Data1!A2:C2)-AVERAGE(Data2!A2:C2)'

If one of the table values, cell H5 returns the value '5', I want my functionion to pick up the value and change the formula to

'=AVERAGE(Data1!A5:C5)-AVERAGE(Data2!A5:C5)'

Is this possible to achieve? thank you so much in advance.

 

[Habtest]

Hi there, if I understand correctly, INDIRECT will work, here is an example:

Book1
	A	B	C	D	E	F
1	A	B	C
2	1	2	3		Row	2
3	2	3	4		Result	2
4	3	4	5
5	4	5	6
6	5	6	7
Sheet1

Cell Formulas
Range	Formula
F3	F3	=AVERAGE(INDIRECT("Sheet1!A"&F2,TRUE):INDIRECT("Sheet1!C"&F2,TRUE))

 

[psg2002]

Hi there, if I understand correctly, INDIRECT will work, here is an example:

Book1
	A	B	C	D	E	F
1	A	B	C
2	1	2	3		Row	2
3	2	3	4		Result	2
4	3	4	5
5	4	5	6
6	5	6	7
Sheet1

Cell Formulas
Range	Formula
F3	F3	=AVERAGE(INDIRECT("Sheet1!A"&F2,TRUE):INDIRECT("Sheet1!C"&F2,TRUE))

This is perfect! Thank you merci!!