Validation

D

DDD

Ok so i want to validate a cell that has a number in it like must be above 0.
I know how to do that.

However, from what i know this only works when the cell has a number entered
by you & not by a formula.

There may be a way to validate it but im sure that this may involve using
Visual basic, & i dont know who to use this.

Heres my question--------------------- --------------------
---------------

Does any one know how to enter a number into a cell by using a formula from
another cell

e.g.

5 entered into A1
3 entered into B1
=Sum(A1:B1) entered into C1 (Answer is 8)
D1 is blank


Ok so instead of validating C1 which has a formula within it, is it possible
to link the answer in C1 to D1 without adding a formula to D1 (Left blank)

This could be by using some feature excel has or modifying the formula in c1
like =Sum(A1:B1),ANSWERIN,D1 etc
 
D

DDD

Also i dont know if this matters but im using Excel 2007, the spreadsheet
this question is concerned within is saved as Excel 97-2003 workbook, this is
because i repeatedly open this between my home (in 2007) & at school (2003)
 
T

T. Valko

You haven't said what the specific validation rule would be. What you're
describing can't be done using Data>Validation. If you want to limit the
result of a formula that's easy enough:

=IF(A1+B1>0,A1+B1,"")
 
D

DDD

Sorry, i wasnt really sure on how to explain this.

The validation rule isnt important here (Extra information)

What i was trying to say is that when you type in a simple formula (Sum
formula) in a cell then you will get the answer to that sum in that same cell.

What i want to know is how do you modify that simple formula, so that the
answer appears in another cell.

This other cell will contain the answer & that is it. No formula , just a
number.

If this cant be done by modifying the formula then is there any other
feature of excel that can do this.
 
T

T. Valko

You need an event macro to do this.

I'm not the best programmer but this will do what you want.

Assume cell A1 contains the formula. You want cell B1 to "mirror" the result
of the formula in cell A1.

Select the sheet where you want this to happen
Right click on the sheet tab and select View Code
Copy/Paste this code into the window that opens:

Private Sub Worksheet_Calculate()
Dim MyCell As Range
Set MyCell = Range("A1")
On Error GoTo Endit
Application.EnableEvents = False
Range("B1").Value = MyCell.Value
Endit:
Application.EnableEvents = True
End Sub

Close the window to return to Excel.

Cell B1 will mirror the result of the formula in cell A1.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top