0

I learned recently about the & and "" and their usage in making an autoupdating formula.

What I mean is, for example I have 3 numbers, 1, 2 and 3 and I sum them in a cell with =B3+B4+B5, then the cell shows 6. But with & and "" used like this =B3&"+"&B4&"+"&B5, the cell shows 1+2+3.

Now what I want is to have a macro that does this automatically, but with all the operators and with unlimited numbers.

Does anyone have something like this macro?

Bogdan
  • 23
  • 5

1 Answers1

0

First a few tips and hints, perhaps relevant to your questions:

  • If you hit CTRL + ` while in a workbook, you can see the contents of all formulas in the workbook. Hit it again to return to normal view.

  • If you hit F9 while editing in the formula bar, the formula will resolve to values. Hit escape to cancel, or enter to confirm (and loose the formula).

  • Use Formulas ribbon > Evaluate Formula to see Excel solve the equation step by step.

  • If you want to evaluate a formula that is written as a text value in a cell (e.g. ="=A1+2"), use the Excel 4.0 macro EVALUATE as a defined named range. For more details see here https://superuser.com/a/1385388/1241807

  • Use the FORMULATEXT function to return a single cells' formula contents as text.

Now, if I understand your question correctly, you want to be able to look up the formula text in any cell (i.e. using FORMULATEXT) and then substitute the references for actual values, returning the whole partially resolved formula as a text string.

A VBA macro can do this relatively easily using regular expressions to identify reference text and then swapping it out with values. It might also be possible to do this using only excel formula, picking out the reference text using SEARCH, SUBSTITUTE AND INDIRECT. The latter could be made a lot easier if you can promise to use only absolute references, as the $ sign can be used as a delimiter of sorts.

ExcelEverything
  • 3,194