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.

Thanks in advance

  • 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:


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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.