Results 1 to 15 of 15
  1. #1
    Silver Lounger
    Join Date
    Aug 2001
    Location
    Canton, Ohio, USA
    Posts
    1,716
    Thanks
    0
    Thanked 1 Time in 1 Post

    Calculation (Office 2000 SR-1)

    I can enter a few different numbers in one cell lined up vertically within the same cell by using
    Attached Files Attached Files
    H Lewton

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculation (Office 2000 SR-1)

    It can certainly done, but it is far from trivial. It involves defined names with XL4 macro functions or complicated array formula's.

    You can save yourself a lot of complexity by simply having each number in a single cell.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

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

    Re: Calculation (Office 2000 SR-1)

    IMHO, what you're doing is going against the grain of Excel. Different values belong in different cells. that makes formatting and calculations much easier.

    Putting line feeds in a cell make it into text, so that numeric formatting will be ignored.

    If you still want to do it as you designed, put the following function in a module:

    Function CellSum(aCell As Range) As Double
    Dim strVal As String
    Dim intStart As Integer
    Dim intEnd As Integer
    strVal = aCell.Value
    ' Get out if cell is empty
    If strVal = "" Then
    Exit Function
    End If
    intStart = 1
    ' Look for line feed
    intEnd = InStr(intStart, strVal, Chr$(10))
    Do While intEnd > 0
    ' Add numeric value
    CellSum = CellSum + Val(Mid(strVal, intStart, intEnd - intStart))
    intStart = intEnd + 1
    intEnd = InStr(intStart, strVal, Chr$(10))
    Loop
    ' Add final (or only) part
    CellSum = CellSum + Val(Mid(strVal, intStart))
    End Function

    Then, you can use a formula like

    =A2*B2-CellSum(C2)

  4. #4
    Silver Lounger
    Join Date
    Aug 2001
    Location
    Canton, Ohio, USA
    Posts
    1,716
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Calculation (Office 2000 SR-1)

    Jan,

    I was fairly certain that would be the case but I thought there just might be a simple solution or function for doing it.

    Thank you.
    H Lewton

  5. #5
    Silver Lounger
    Join Date
    Aug 2001
    Location
    Canton, Ohio, USA
    Posts
    1,716
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Calculation (Office 2000 SR-1)

    Hans,

    Thank you very much. I may try it but I think your first suggestion of keeping things simple is probably best for me. However knowing me as I do, I will probably experiment with your module and see what kind of trouble I get myself into.

    Many thanks.
    H Lewton

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Calculation (Office 2000 SR-1)

    To get the effect you are using in your sreadsheet, you could use merged cells. See attached.

    Andrew C.
    Attached Files Attached Files

  7. #7
    Silver Lounger
    Join Date
    Aug 2001
    Location
    Canton, Ohio, USA
    Posts
    1,716
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Calculation (Office 2000 SR-1)

    Andrew,

    Thanks. I checked it out and see what you have done. It works as I wanted. I did, however already get HansV code working and it works real slick also.

    I appreciate all the help. These forums are fantastic.
    H Lewton

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

    Re: Calculation (Office 2000 SR-1)

    Hi hlewton,

    My code was actually just a lark, to show that it could be done. However much you may like it, you still have the problem that a cell with line feeds is always considered to be text. You can't format the individual numbers in it. So I think Andrew's solution is to be preferred.

    Regards,
    Hans

  9. #9
    Silver Lounger
    Join Date
    Aug 2001
    Location
    Canton, Ohio, USA
    Posts
    1,716
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Calculation (Office 2000 SR-1)

    HansV,

    Thanks, I hadn
    H Lewton

  10. #10
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculation (Office 2000 SR-1)

    You should be aware however, that merged cells may raise all sorts of trouble in Excel, regarding copying, sorting, etcetera. Also, I was told some array formula's will not work with merged cells (although you will probably never use them).

    It might be simpler to make the sheet's grid invisible (Tools, OPtions, View, Gridlines) and create a new "grid" using borders in the places you want them.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  11. #11
    Silver Lounger
    Join Date
    Aug 2001
    Location
    Canton, Ohio, USA
    Posts
    1,716
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Calculation (Office 2000 SR-1)

    Jan Karel Pieterse,

    Thank you. I hadn
    Attached Files Attached Files
    H Lewton

  12. #12
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculation (Office 2000 SR-1)

    In F2 enter:

    =A2*B2-SUM(EVAL("{"&SUBSTITUTE(C2,CHAR(10),",")&"}"))

    In order to use EVAL you need to download the Longre's Morefunc add-in from:

    http://longre.free.fr/english/index.html

    Aladin
    Microsoft MVP - Excel

  13. #13
    Silver Lounger
    Join Date
    Aug 2001
    Location
    Canton, Ohio, USA
    Posts
    1,716
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Calculation (Office 2000 SR-1)

    Aladin,

    I have the functions downloaded and EVAL appears in the Addins, so they are now on my machine and; In F2 enter:
    =A2*B2-SUM(EVAL("{"&SUBSTITUTE(C2,CHAR(10),",")&"}"))
    does the calculation just fine. Wish I understood more about these types of formulas.

    Thanks.
    H Lewton

  14. #14
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculation (Office 2000 SR-1)

    As you noticed, sorting will give trouble with merged cells. It will cause you a lot of headaches trying to set things up with merged cells to be able to sort. Simplest is to NOT merge.

    Another problem with your setup -if you want it sorted- is that for a proper sort you will need to have the name repeated accross each row. Otherwise the empty rows will end up at the bottom or the top of your sorted list, disconnected from the persons they belonged to!
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  15. #15
    Silver Lounger
    Join Date
    Aug 2001
    Location
    Canton, Ohio, USA
    Posts
    1,716
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Calculation (Office 2000 SR-1)

    Jan Karel Pieterse,

    Thanks. I have been trying different sorts with the merged cells and, as you say, they do cause headaches. I think I
    H Lewton

Posting Permissions

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