0

Based on column A TRUE/FALSE values column B will display "True" or "False", formula=IF(A1;"True";"False"). Formula is copied (by dragging) to multiple rows below.

How do I change the formula in all rows in B to =IF(A1;"Sooo true";"False") without dragging or selecting the column B? I would like to click cell B1, change the text in formula and propagate that change to all cells in column B that copied the formula, so that in all cells that previously was "True" now would be "Sooo true".

Lets assume that cell B5 has value entered by hand ="INACTIVE CELL" that is meant to stay unchanged (dragging would overwrite that cell's value back to formula).

Jakub Sengerski
  • 3
  • 1

1 Answers1

1

Unless there is a third-party add-on, I don't think Excel has such a capability (but I may be wrong). The picture below illustrates two possible solutions:

  1. (columns A,B in picture) In the original setup, configure it so that the values returned by the formula on not hard-coded in the formula but rather reference another cell you can change.
  2. Select the cells with the formula in question, press ctrl+h (find and replace), enter the hard-coded text in the formula you want to change and then the text you want to replace it with. The, press "replace all"

enter image description here

Brian
  • 977
  • 1
  • 9
  • 24
  • Thank you, both of these solutions helps a bit in my case! Expanding on solution 1 where you insert a reference to a value, is it possible to insert a reference to a formula? Or maybe a way convert a text value from cell into a formula? There would be one cell with the formula 'template' ```IF(A;"True";"False")``` and this would somehow end up as ```IF(A1;"True";"False")``` in row 1, ```IF(A2;"True";"False")``` in row 2 and so on. – Jakub Sengerski May 22 '19 at 21:31
  • I am unsure about this. I will ponder on it but I don't know how off the top of my head. I did find another post that may be what you are looking for: https://superuser.com/questions/253353/excel-function-that-evaluates-a-string-as-if-it-were-a-formula – Brian May 23 '19 at 00:01