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 in Others by Kithuzzz
• 20,660 points
32 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 by narikkadan
• 37,660 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 in Others by gaurav
• 22,040 points
506 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 in Others by narikkadan
• 37,660 points
52 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 in Others by narikkadan
• 37,660 points
25 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 in Others by narikkadan
• 37,660 points
28 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 in Others by Edureka
• 13,640 points
137 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 in Others by Edureka
• 13,640 points
230 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 in Others by Edureka
• 13,640 points
101 views
0 votes
1 answer

IF - ELSE IF - ELSE Structure in Excel

In this case, you can use nested ...READ MORE

answered Feb 18 in Others by gaurav
• 22,040 points
68 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 in Others by narikkadan
• 37,660 points
63 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 in Others by narikkadan
• 37,660 points
77 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