Is there a way to get the value of a cell in google sheets and use it as a row in another formula

Question

enter image description here enter image description here

=average('Data Sheet'!H(C2):H(C3))

I want to get the average of a range of data from one sheet and put it into another sheet. I want to make it so that I can adjust the timeline. So I matched the dates and then got the row number. Now I want to use the column header that I know I want 'H' and put that together with the row number that is a variable that I store in C2 and C3 in the database sheet.

I tried to use several formulas I thought that I could just put parenthesis around the cell I wanted to get the value and it would figure out that I just wanted the number. I am new with sheets so it probably is an easy fix.

Answer 1

You may try:

=average(indirect("'Data Sheet'!H"&C2&":H"&C3))

OR this if you wish to disregard the helper cells (C2, C3) and directly go with dates in B2,B3

=averageifs('Data Sheet'!H:H,'Data Sheet'!A:A,">="&B2,'Data Sheet'!A:A,"<="&B3)