How to use the value in a cell as the cell number in a formula

[stha_manea]

Hello everyone!

This is just a sample of my datasheet.

I have the hourly data (Column B), which I should use to calculate the daily data (Column C). I need to create a template such that every time I change the values in column B, the values in C and E will also change automatically. For now, I have thought of writing the formula =C50, =C74, and so on.. for 365 days, which is a bit tedious. I am sure there must be a simpler way. Is there a way I could use the values in Column D (2, 26, 50, 74) in the formula in Column E? Or is there any other lookup or reference function that allows me to do so?

Using Pivot table and GETPIVOTDATA can create the column in the desired form, but once I create the pivot table for 2018, it doesn't get updated if I add the data of 2019 in the column A and B, meaning that I have to create Pivot table every time for other years.

Thank you in advance!

 

[CA_Punit]

You can use Indirect function
=Indirect("C"&D4)

 

[jasonb75]

You can use Indirect function

Or you could use INDEX instead of making it volatile, either way the suggestion is moot if you read the rest of the question.

An alternative method
In C2

=IF(INT(A2)=INT(A1),"",INT(A2))

In D2

=IF(ROWS(E$2:E2)>COUNT(C:C),"",SMALL(C:C,ROWS(E$2:E2)))

In E2

=IF(D2="","",SUMIFS(B:B,A:A,"<"&(D2+1)-SUM(E$1:E1))

 

[stha_manea]

Thank you so much for your help! The INDIRECT function was exactly the function I was looking for.

You can use Indirect function
=Indirect("C"&D4)

 

[stha_manea]

Or you could use INDEX instead of making it volatile, either way the suggestion is moot if you read the rest of the question.

An alternative method
In C2

=IF(INT(A2)=INT(A1),"",INT(A2))

In D2

=IF(ROWS(E$2:E2)>COUNT(C:C),"",SMALL(C:C,ROWS(E$2:E2)))

In E2

=IF(D2="","",SUMIFS(B:B,A:A,"<"&(D2+1)-SUM(E$1:E1))

Thank you for your suggestions! I will consider these for future use. For now, I used the INDIRECT function.

 

[jasonb75]

For now, I used the INDIRECT function.

INDIRECT is a function that is often misused and suggested where it is not needed, @CA_Punit's suggestion here is a prime example.

INDIRECT is a volatile function, INDEX is not. Volatile functions waste effort by recalculating their results when it is not necessary to do so. If you have a lot of them, you will find that your workbook can become unusable very quickly.

=INDEX(C:C,D4)

For now, I have thought of writing the formula =C50, =C74, and so on.. for 365 days, which is a bit tedious.

From the screen captures, it would appear that you are manually entering the numbers into column D which would defeat the object of trying to make it less tedious. The method that I suggested automates the entire process.

It shows date instead of sum in column C, but it was unclear from your post if the sum in column C was required, or if it was simply there as an intermediate step for your own efforts.