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

1. ## 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.

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:

Thank you for the help!

2. ## Re: How do you use a Cell Reference once you do Address?

Is this the one?

3. ## Re: How do you use a Cell Reference once you do Address?

I don't believe you should need CELL function. This should do what it appears you want.

Formula:
`Please Login or Register  to view this content.`

4. ## Re: How do you use a Cell Reference once you do Address?

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)?

5. ## Re: How do you use a Cell Reference once you do Address?

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.

6. ## Re: How do you use a Cell Reference once you do Address?

I agree with John. This requires a sample workbook.

7. ## Re: How do you use a Cell Reference once you do Address?

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!

8. ## Re: How do you use a Cell Reference once you do Address?

This proposed solution employs some helper cells, which may be hidden for aesthetic purposes.
The formulas in cells G2:G3 look up the 'Start' and 'End' numbers using Vlookup.
The formulas in cells H2:H3 find the row numbers using formulas similar to: =SUMPRODUCT((A\$1:A\$100=G2)*ROW(\$1:\$100))
The formula in G1 is:
Formula:
`Please Login or Register  to view this content.`

Let us know if you have any questions.

There are currently 1 users browsing this thread. (0 members and 1 guests)