Thread: Calculations 2 (Excell office XP)

1. Calculations 2 (Excell office XP)

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

2. 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.

3. 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

4. Re: Calculations 2 (Excell office XP)

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

5. 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. 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. 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.

Posting Permissions

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