How do you use a Cell Reference once you do Address?

I have a dynamic table in a column that counts down numbers (think 29, 28, 27, 26, 25... 4, 3, 2, 1, 0, -1, etc). These numbers will constantly change, and I'll constantly want different numbers based on a criteria.

I've applied CELL and INDEX(MATCH) to get the Cell references I need. In this example I'm grabbing 4 and 29 (would be dynamic in the final formula, but I don't need help there) based on the MATCH.

=CELL("address",INDEX($C$101:$C$166,MATCH(29,$C$101:$C$166,0))), which will return: $C$112

=CELL("address",INDEX($C$101:$C$166,MATCH(4,$C$101:$C$166,0))), which will return: $C$137

A final result would function like =SUM($C$112:$C$137). When I try to combine the formulas it won't work, what am I doing wrong? How I'm thinking of the combination currently is below:

=SUM(CELL("address",INDEX($C$101:$C$166,MATCH(29,$C$101:$C$166,0))):CELL("address",INDEX($C$101:$C$166,MATCH(4,$C$101:$C$166,0))))

Thank you for the help!

Is this the one?

=SUM(INDIRECT(CELL("address",INDEX($C$101:$C$166,MATCH(29,$C$101:$C$166,0)))&":"&CELL("address",INDEX($C$101:$C$166,MATCH(4,$C$101:$C$166,0)))))

Thank you all for the help! mrshl9898 your solution worked perfectly for the base request, but I made the example too simplistic for what I really needed. After returning the cell, I then need to OFFSET it by X columns. FlameRetired, that's why I was seeking to apply the CELL function.

When I try to apply the OFFSET I can get the value, but then I can't get the SUM (I'm actually going to being use RSQ, but for the example SUM is easier to work with) to behave correctly.

What am I doing wrong (in this example I'm trying to move 6 columns over from the returned cell)?

=SUM(OFFSET(INDIRECT(CELL("address",INDEX($C$101:$C$166,MATCH(4,$C$101:$C$166,0)))),0,6)&":"&SUM(OFFSET(INDIRECT(CELL("address",INDEX($C$101:$C$166,MATCH(29,$C$101:$C$166,0)))),0,6)

Attach a sample workbook (not image).

Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.

Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Thought we could get away with it, but I guess not this time. See the attached example.

There are three tabs. The first two are examples of how the output will change based upon a drop-down. In the real file there are +1000 of these cases contained to a single sheet, which is why the array for the RSQ formula needs to be dynamic.

The third tab is a reference table. The formula will know which periods/rows it needs based on the reference table and the drop-down.

Finally, once the rows are known, the formula needs to move cells over to perform the RSQ calculation on the dynamic dataset.

Thanks for the help!