# Excel: Use cell reference within formula for formula in another cell

I have joined just to ask this question as I'm not sure if it's achievable without VBA.

In column A I have a list of values, let's say A1:A9 contain the values "1000" to "9000" respectively.

In B1 I am counting the values like so (with a text addition to cell) ="SDLT "&COUNT(A4,A7)&" units" with the result "SDLT 2 units".

My question is how can I have C3 display the combined values of the cells referenced in B1? (result should be "11000" in this example).

I have played around with INDIRECT and MID to try and find the references but to no avail.

• The best approach would be to not use a hardcoded range in B1 (and then try to find and cut it out for reuse), but instead use a reference in B1 and the same one in C3. Even better, don't use a reference, but code the selection criteria (meaning write a formula that finds A4:A7 according to the selection rule in your head instead of writing A4:A7), but that might not be an option. – Aganju Dec 20 '16 at 11:58
• Not sure you really should want to do this, but check out `FORMULATEXT()`, which you could combine with some string handling functions to fetch the cells referenced in the formula in `B1` dynamically. – 3N1GM4 Dec 20 '16 at 12:25
• Aganju your change of logic makes sense. For my application I would need to pull a specific number (2 in the example) of "Random" values from column A. "Random" in this case could be defined in a number of ways, but a simple one that comes to mind would be starting from the largest value and working down and this could be done using the LARGE function for example... – Roman Dec 20 '16 at 13:54
• B1 "SDLT "&(COUNT(LARGE(A1:A9,3),(LARGE(A1:A9,6))))&" units" C1 LARGE(A1:A9,3)+(LARGE(A1:A9,6)) This would work for the example and would work for my application but requires editing the formula depending on how many values are needed from column A. – Roman Dec 20 '16 at 13:55

As per my comment, you could for example do this in `C1`:

``````=SUM(INDIRECT(MID(FORMULATEXT(B1),FIND("(",FORMULATEXT(B1))+1,2)),INDIRECT(MID(FORMULATEXT(B1),FIND(")",FORMULATEXT(B1))-2,2)))
``````

This makes a few assumptions:

• There are only ever two cells specified in the formula in `B1`
• The list of values in column A never extends beyond row 9
• The formula in `B1` only ever contains a single pair of brackets

These could also be worked around, but hopefully the above at least gives you a starting point for a solution.

• 3N1GM4 that works for my example and is very close to what I need! Point three of your bullets is true, the only variation would be additional values added to the COUNT function. Point 1 and 2 ideally would need to be variable in my application. I have not had time to review your formula and am unfamiliar with FORMULATEXT so can't comment as to whether or not the above is achievable but I will have a look into it – Roman Dec 20 '16 at 14:03
• `FORMULATEXT()` just returns the formula from a cell as a string - I didn't know about it until today either. :) I expect you can work around the other limitations of my suggested solution, as I mentioned in my answer, I just haven't had time to do that myself. – 3N1GM4 Dec 20 '16 at 14:12