Control cell value from another cell in Excel

0 votes

Having this image:

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:

image

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 by Kithuzzz
• 38,000 points
1,410 views

1 answer to this question.

0 votes

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.

answered Nov 10, 2022 by narikkadan
• 63,600 points

Related Questions In Others

0 votes
1 answer

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

answered Feb 9, 2022 in Others by gaurav
• 23,260 points
1,648 views
0 votes
1 answer

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

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

answered Oct 31, 2022 in Others by narikkadan
• 63,600 points
1,544 views
0 votes
1 answer

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

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

answered Nov 7, 2022 in Others by narikkadan
• 63,600 points
1,500 views
0 votes
1 answer

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

answered Nov 8, 2022 in Others by narikkadan
• 63,600 points
817 views
0 votes
1 answer

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

answered Dec 17, 2022 in Others by narikkadan
• 63,600 points
1,631 views
0 votes
1 answer

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

Hello, yes u can find your birthdate using ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,690 points
4,140 views
0 votes
1 answer

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

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

answered Feb 16, 2022 in Others by Edureka
• 13,690 points
2,014 views
0 votes
0 answers

Convert Rows to Columns with values in Excel using custom format

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

Feb 17, 2022 in Others by Edureka
• 13,690 points
995 views
0 votes
1 answer

Unable to import data in excel from another website using VB code

Replace : Set ieTable = ieDoc.all.Item("report-table") With: Set ieTable = ...READ MORE

answered Sep 21, 2022 in Others by narikkadan
• 63,600 points
805 views
0 votes
1 answer

Extract unique value from the range in Excel

The portability of spreadsheet functions like UNIQUE() ...READ MORE

answered Sep 23, 2022 in Others by narikkadan
• 63,600 points
1,294 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP