Control cell value from another cell in Excel

Question

Having this image:

Is there some way of having something like a "control cell"? E.g.: if B1 (the control cell) has some text, B3 displays "HELLO"; if B1 is empty, B3 displays "NONE". I can solve it with an IF function in B2, but if I write something in this B2, I will lose the IF formula, so I need to control it from another cell.

If that isn't clear enough, here's another example:

I want to show "?" in all cells under Exam 3 only if D1 has text. Otherwise, they will be blank. I want to put grades in these cells, but if I remove the grade, I want to recover the "?" value, so I think I need to control this value from another cell in order to not lose the formula.

Answer 1

You may accomplish this by using two tables—basically, a summary table—one for input and the other for viewing. Then, all that would be required of your formula is an IF statement and a COUNTA.

You might try the following for the Formula in C2 in the summary table:

=IF(COUNTA('Input Table'!$B2:C2)<1,"?",'Input Table'!C2)

This calculation assumes that the page labelled "Input Table" contains the INPUT data in the same columns as the snapshot you provided.

The only difference is that you would extend the references in Cell D2:

=IF(COUNTA('Input Table'!$B2:D2)<1,"?",'Input Table'!D2)

Ideally though, as another contributor said though, the best way is VBA if you are able.