Placing or forcing the contents of one cell or results of an equation into another cell.

In anticipating a design process, If A1 is blank, is there a function I can write in B1 that places the result into A1?
LVL 1
Bill GoldenExecutive Managing MemberAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SteveCost AccountantCommented:
Functions cannot push a result to another cell.
The process works by pulling information into cell functions.
So short answer, no there is not a function.

You could use VBA to check the sheet as you make changes.
But this is not strictly a 'function'.
But I think you could likely get it to do what you need if you want to go down the coding route.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Tj aCommented:
I agree with Steve. Formulas are generally exclusive to the cells that contain them. You could always use conditional formatting to visually do something to a cell if it's blank but I know that's not really what you want.
ThomasMcA2Commented:
Why does B1 have to "populate" A1?

Would a default value in A1 do what you want?

Perhaps you should explain what you need to accomplish, and then an expert can describe your options.
OWASP: Forgery and Phishing

Learn the techniques to avoid forgery and phishing attacks and the types of attacks an application or network may face.

Bill GoldenExecutive Managing MemberAuthor Commented:
I have a column in which each cell has a formula...

=IF(J21="","",IF(K19<>0,SUM(K18:K20)*INDEX(AA$11:AA$19,J21,0)))

BUT the operator will, more often than not, enter a dollar amount in the cell.  The problem is when they change their mind and remove their entry, the resident formula is gone.

Any thoughts?  As a backup, I am launching a question about testing for either the formula or an entry to the cell.  That would at least alert the data entry person that the cell in not in a normal state.
ThomasMcA2Commented:
VBA can be used to enforce consistency within the worksheet. It can watch for changed cells, and re-apply the formula if the cell is empty.

Which row number did your formula come from?
Which column did the user change?

The answer to both of those questions are needed to finish the below VBA code.

Let's say they changed column J. Something similar to this will work:

Private Sub Worksheet_Change(ByVal Target As Range)
     If Not Application.Intersect(Target, Me.Range("J:J")) Is Nothing Then
          ' At least one cell of column J was changed.
          ' Carry out some action. 
     End If
End Sub

Open in new window

Bill GoldenExecutive Managing MemberAuthor Commented:
Everyone started me thinking about how to accomplish the task in by another method. The entry will be made in A1,  A2 will display certain amounts based on A1 and A3 will monitor the results signaling a problem, if there is one.  Sort of the long way around the problem but accomplishes the task.  Thanks guys.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.