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

psg2002

New member
Joined
Feb 15, 2021
Messages
3
Reaction score
0
Points
0
Excel Version(s)
Office 365
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 formula 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 formula 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.
excel_screenshot.png


 
Try:
Code:
=AVERAGE(INDIRECT("Data1!A" & H5 & ":C" & H5))-AVERAGE(INDIRECT("Data2!A" & H5 & ":C" & H5))
 
Sorry about that, I will take note of this going forward!
 
Back
Top