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

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

  • 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 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: superuser.com/questions/253353/… – Brian May 23 at 0:01

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.