Solved

Excel Changing cell value based on another cell, but not always

Posted on 2013-11-15
4
4,693 Views
Last Modified: 2014-05-15
Is there any way to have a cell change its value based on another cell, but also be able to enter a cell value if the first cell had nothing in it without wiping out a formula?

Ex:

If Cell A1 has a value of 1
I want Cell B1 to have a value of 1 automatically.

But if Cell A1 has no value,
I want Cell B1 to be able to have a value put in without overwriting the formula.

Is that at all possible? Perhaps by having a formula in another cell, say C1, that is monitoring the value of A1, and if it has a value, place that value in B1?

Thanks.
0
Comment
Question by:repco
4 Comments
 
LVL 35

Accepted Solution

by:
[ fanpages ] earned 167 total points
ID: 39652422
Hi,

A single cell can have either an explicit value (such as a number, text, or a date), or a value derived from the presence of a formula.

It cannot have both a value & a formula (unless the value is the result of the formula).

That is, it cannot have both one value & a formula that results in another value.

However, Visual Basic for Applications [VBA] code can be introduced into your workbook so that whenever another cell's value changes, the value of cell [B1] can change to suit your needs.

This could be either an explicit value, or a value derived from a formula (but not both at once).

The VBA code within the Worksheet_Change() event would determine what [B1] contained.

Please advise if you would wish to see a demonstrate of this.  If possible, could you attach (upload) your existing workbook so that the necessary code can be added to this directly (& then re-attached for you to download)?

BFN,

fp.
0
 
LVL 14

Assisted Solution

by:sentner
sentner earned 166 total points
ID: 39652466
Easiest way would probably be to have another cell contain the data for the default.

For example, setting B1 to:

=if(A1=1,1,C1)

This would put a 1 if A1=1, otherwise it'd use the value of C1.
0
 

Author Comment

by:repco
ID: 39652520
Thanks for the input. In the end, I'll just have to change the way the form works as I cannot use VBA, nor does it have a default value. VBA will work though, so I'll give you points for that.
0
 
LVL 14

Assisted Solution

by:frankhelk
frankhelk earned 167 total points
ID: 39656144
A simple way for that would be the use of an intermediate cell for that.

Imagine A1 is your "input" cell, which could contain an value or be empty.
Cell A2 (which supplies the value to subsequent formulas) could contain a formula like this:

=IF(A1="";(formula);A1)

or

=IF(ISEMPTY(A1);(formula);A1)

That way the value of A1 is supplied to the subsequent formula if it is present, and the formula result if A1 is empty.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Hiding column macro 10 28
What is format f12.8 for a CSV file 6 41
Help with Excel formula 6 38
Converting An Excel IF statement formula to   VBA 10 26
Microsoft Office Picture Manager is not included in Office 2013. This comes as a shock to users upgrading from earlier versions of Office, such as 2007 and 2010, where Picture Manager was included as a standard application. This article explains how…
Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now