Results 1 to 7 of 7

20051010, 08:08 #1
 Join Date
 Apr 2001
 Location
 Winchester, Hampshire, United Kingdom
 Posts
 64
 Thanks
 1
 Thanked 1 Time in 1 Post
Covert Formula to Value on the fly (Excel 2003)
Is it possible to convert data calculated from a formula in one column to values in another column without having to do this manually, and without creating a circular reference? I have a formula calculating a bank balance, but then need to use this figure to calculate interest on that balance, which then changes the balance itself. I can do that manually by copying values, but want to automate the process if possible.
grateful for any help on this one!

20051010, 08:13 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: Covert Formula to Value on the fly (Excel 2003)
You could create a macro to do this.
 Do you want the values to be in the column immediately to the right of the column with the formulas, or somewhere else?
 Is it OK if the macro uses the selected cells, or should it try to figure out which cells to use?

20051010, 10:14 #3
 Join Date
 Apr 2001
 Location
 Winchester, Hampshire, United Kingdom
 Posts
 64
 Thanks
 1
 Thanked 1 Time in 1 Post
Re: Covert Formula to Value on the fly (Excel 2003
Hi Hans, thanks for the reply. It doesn't really matter where the result column is  it could be to the right, or even on another sheet, but right is fine. It is ok to use the selected cells, but the bank balance only shows when another data entry cell is completed  the worksheet is built up week by week over the year. Presumably the macro could "trigger" when that data cell is filled? It is simply a week number, as this was the easiest way of creating a condition to complete a number of other calculated cells. I enter the week number when that week's entry data is available, then enter the other data, and that then fills calculated cells which go to create the bank balance  then the interest is calculated (to be added via the macro).
Could the macro be made conditional each week on a new week number being entered, or would that make it too complex? It seems odd there isn't a function to copy values from a formula as the formula is calculated, as this must be a common requirement!
I have attached a working sample of the worksheet. I would prefer to restrict data entry to the first sheet ("Y1_Cases") if possible (data entry in cols A,C,D,F,G and J). Everything else shouild ideally then follow from that. The interest calculation is on the second sheet ("Y1_Overheads") columns S and T. I hope it all makes sense  if not please ask.
thanks again for your help.

20051010, 10:32 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: Covert Formula to Value on the fly (Excel 2003
Thanks for posting a workbook, but it's not clear to me which values you want to be copied. Should the result of the formulas in column S on Y1_Overheads be copied to column T? Or something else? Please explain, and keep in mind that I don't have the slightest idea what your spreadsheet does.

20051010, 11:55 #5
 Join Date
 Apr 2001
 Location
 Winchester, Hampshire, United Kingdom
 Posts
 64
 Thanks
 1
 Thanked 1 Time in 1 Post
Re: Covert Formula to Value on the fly (Excel 2003
Hans, sorry  what's obvious to me isn't to others! It is column T that needs to be created as values from column S on Y1_Overheads. Column R copies the bank balance from another sheet (not shown as I had to reduce the size of the sample); Col S then calculates interest based on R. I then need to add this figure to column R to give the correct bank balance after interest, but this creates a circular reference unless the values in S are converted to pure values, independent of R. I am doing this manually into col T, but this is the process I would like to automate for each week.
regards
Richard

20051010, 12:15 #6
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: Covert Formula to Value on the fly (Excel 2003
Try this:
 Rightclick the sheet tab of Y1_Overheads.
 Select View Code from the popup menu.
 Copy the following code into the worksheet module:
<code>
Private Sub Worksheet_Calculate()
Dim i As Long
Application.EnableEvents = False
For i = 3 To Range("S65536").End(xlUp).Row
Range("T" & i).Value = Range("S" & i).Value
Next i
Application.EnableEvents = True
End Sub
</code>
Any time the worksheet is recalculated, this event procedure will copy the values of column S into column T.

20051010, 12:34 #7
 Join Date
 Apr 2001
 Location
 Winchester, Hampshire, United Kingdom
 Posts
 64
 Thanks
 1
 Thanked 1 Time in 1 Post
Re: Covert Formula to Value on the fly (Excel 2003
That seems to work Hans, and it's great. Not only have you solved my problem, but I've learnt a lot as well. Thanks  it is appreciated.
regards
richard