Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Soldotna, Alaska, USA
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Calculations 2 (Excell office XP)

    I have attached the form so you can play with it from my previous question.
    Attached Files Attached Files

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculations 2 (Excell office XP)

    The answer is - sort of.

    I have attached your workbook with formula that do sum the amounts. However, there are a number of drawbacks:

    1- It would be next to impossible to suppress display of leading zeros in the sum.

    2- The formula get a bit messy, and will be difficult to change in the future if that becomes necessary.

    3- This will not catch illogical data entry. For example, if someone enters an amount but leaves a cell in the middle of the amount blank.

    I would strongly suggest changing the design of this form to put entities in a single cell, and not spread them over several cells. You can't get the nice boxes around each character of the data, but I think it will save you much grief in the future.
    Attached Files Attached Files
    Legare Coleman

  3. #3
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Calculations 2 (Excell office XP)

    Hi Dale,
    Take a look at the attached. It's pretty close to what you're after, and is achieved via custom formatting (?|?|,|?|?|?|,|?|?|?|.|?|?), text formatting (Courier New), cell formatting (Vertical|Centre) and merging the value boxes you had. With this solution you don't have to worry about the issues that Legare identified.
    Cheers
    Attached Files Attached Files
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  4. #4
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Soldotna, Alaska, USA
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculations 2 (Excell office XP)

    Thanks a million, I think you can see whay I was kind of confused!

  5. #5
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Soldotna, Alaska, USA
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculations 2 (Excell office XP)

    I just can't tell you how greatful I am what a bear to figure out the right code for this crappy of a form design. The bosses have to keep this form the way it is and I was not able to figure out a way to make life easier.

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Calculations 2 (Excell office XP)

    Another way to do it is to have the input be put in NORMALLY in another place in a form that makes more sense to excel (even use a userorm if desired)

    From the inputted data fill out the form to be PRINTED out in the "crappy form design" using formulas.

    Steve

  7. #7
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Calculations 2 (Excell office XP)

    Hi Dale,

    Glad to be of help. Taking the custom format one step further, you could format the cells as:
    +|?|,|?|?|?|,|?|?|0|.|0|0;-|?|,|?|?|?|,|?|?|0|.|0|0;?|?|,|?|?|?|,|?|?|?|.|?|?
    This will put +/- signs in for non-zero values. Also, with the previous solution I gave you, only non-zero decimals were displayed. This change fixes that, too.

    Cheers

    PS: With both solutions, the empty cells have to be 'seeded' with zeros to get the custom format to display.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

Posting Permissions

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