A cell may either have a formula in it that changes results when some part of
that formula changes, or it can have a value that does not change. It cannot
have both.

To save the calculated area results you will need a Visual Basic solution,
and I have written one. Based on what you have written here, the only change
you will have to make in the code is to change one line in it. In the code
where it says:

Const firstResultsCell = "E2" ' change as required
change the E2 to the address of the cell where you were putting your
=IF(A1=40;A2;"") formula. Example: if you had been putting that formula
into cell G5, change the E2 to G5 so that it reads
Const firstResultsCell = "G5" ' change as required

What this will do is to put each new result into the first empty cell in the
column identified by the E2 (or G5) and keep adding new results below that as
you change the angle. You may select and delete those saved results any time
you wish to start a new series of saved results.

To put this code to use, follow these instructions:
Open the workbook, and select the sheet where you perform the iterations and
on which you wish to save the results.
RIGHT-click on the worksheet's name tab, which will bring up a list of
options.
Choose [View Code] from that list of options.
Copy the code below and paste it into the big empty code module that is
presented to you when you choose [View Code].
Make the change to the value I discussed earlier (E2 to G5 or what ever you
need it to be)
Close the Visual Basic editor.

It will work automatically for you at that point. Your Macro security will
need to be set to Low or Medium for this to work. If you need help resetting
macro security, let us know here and we will walk you through that process.

Here is the code to place into your worksheet:

Private Sub Worksheet_Change(ByVal Target As Range)
'address of the first cell to save results into
'all cells below it must not be used for anything
'other than saving results
Const firstResultsCell = "E2" ' change as required

'cell address where you enter the angle
Const angleEntryCell = "A1"
'address of the cell with the calculated area
Const areaResultCell = "A2"
Dim nextResultsRow As Long

If Application.Intersect(Target, _
Range(angleEntryCell)) Is Nothing Then
'did not change the angle
Exit Sub
End If
If Target = 0 Then
'cleared the entry, do not save new result
Exit Sub
End If
'save the results in the first empty
'cell below 'firstResultsCell'
'or in 'firstResultsCell if it is empty
nextResultsRow = Cells(Rows.Count, _
Range(firstResultsCell).Column).End(xlUp).Row + 1
If nextResultsRow < Range(firstResultsCell).Row Or _
IsEmpty(Range(firstResultsCell)) Then
nextResultsRow = Range(firstResultsCell).Row
End If
Range(firstResultsCell).Offset(nextResultsRow - _
Range(firstResultsCell).Row, 0) = _
Range(areaResultCell)
End Sub



"Tunja" wrote:

Hello,

I am using Excel Pro 2003 version. I have an iteration sheet with 2
variables which are Angle and Area.

First I set the angle constant and have the iteration solved the area. Then
I increase the angle and iteration solves the area for the new input angle.
While I am doing the iteration, I want to have the angle and area results to
be saved in result sheet.

For instance for
angle=40, solved area=500, for
angle=41, solved area=550...and so on.

What kind of formula I need to write in the "result" cell so I can have the
angle and area values to be kept there while iteration goes on. I tried to
write with IF statements but when the angle changes area is changing so does
my "result" cell that suppose to keep the results.

I tried to write like this:
A1, where the angle is
A2, where the area is

=IF(A1=40;A2;"")
=IF(A1=41;A2;"")
I want to keep the results so "" part is wrong but I do not know what to
write there so I can keep the results.

Thank you very much.