Results 1 to 7 of 7
  1. #1
    Star Lounger
    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!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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?

  3. #3
    Star Lounger
    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.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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.

  5. #5
    Star Lounger
    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

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Covert Formula to Value on the fly (Excel 2003

    Try this:
    - Right-click 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.

  7. #7
    Star Lounger
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •