creating simple program in excel...

November 16, 2006 at 00:15:12
Specs: XP

Hello. I only know the basics in excel - pls help me with this one.

I have 3 columns, item # (e.g A,B, C..etc),description, Unit price.
I would like to create an option box such that I can enter 2 or more item #s and it will automatically sum up the amount.

it is somehow similar to below
option 1
: A pcs: 300 Price: _____
: D pcs: 150 Price: _____
total Amount:


option 2
: D pcs : 200 Price: _____
: E pcs : 121 Price: _____
: Y pcs : 201 Price: _____
total Amount:


thanks..



See More: creating simple program in excel...

Report •


#1
November 16, 2006 at 02:28:59

You have three columns and lets say that row 1 contains your column names and your first set of items starts in row 2 so in the 4th column insert your piece count and in the 5th column insert the formula:
E2=if(D2<>"",D2*C2,"")
Meaning that if you enter no data (piece count) next to an item then it will display no result otherwise it will calculate the total cost of the amount of pieces per the unit cost.

Now, lets say that you only have items listed down to row 26 then in row 27 you enter the formula:
E27=SUM(E2:E26)
You could alternatively enter the formula:
E1=SUM(E2:26) instead to show the total at the top.
This would show the total cost of all items where you inserted a piece count.

To go a step further; you could use your sheet as your data base and use a separate sheet as an invoice template where it would use lookup formulas geared off of the item numbers to bring in the data from your data sheet and similarly calculate the total costs.

Excel ('97 anyway) comes with a template for this. Go to File, New, Spreadsheet solutions, Invoice. I have not looked but MS likely has many similar templates available on thier site.

HTH
Bryan


Report •

#2
November 16, 2006 at 02:32:08


Report •

Related Solutions


Ask Question