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.

Nov 10, 2022 in Others 906 views

## 1 answer to this question.

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.

• 63,220 points

## Change date format of cell in excel from dd.mm.yyyy to yyy/mm/dd ( excel version 2013 )

Hello :)   Excel’s Format Cells function can quickly ...READ MORE

## Formula for inserting a thumbnail picture into excel cell, based on another cell's value

Here is a really excellent tutorial on ...READ MORE

## How do I combine the first character of a cell with another cell in Excel?

Try this: =CONCATENATE(LEFT(A1,1), B1) READ MORE

## MS Excel showing the formula in a cell instead of the resulting value

Make sure that... There's an = sign before the formula There's ...READ MORE

## Return blank cell only if referred cell is blank, but return aging if date is entered

Try this: =IF(ISBLANK(AC2),"",TODAY()-AC2) The TODAY  function automatically refreshes based ...READ MORE

## Calculate Birthdate from an age using y,m,d in Excel

Hi To Calculate the date, we can ...READ MORE

## Convert Rows to Columns with values in Excel using custom format

1 I having a Excel sheet with 1 ...READ MORE