Invoice in Excel

TheBuGz

Spammer
Joined
Jan 25, 2004
Messages
367
Here is what i am trying to make an Invoice like this
Order.xls
ABCDEFGHIJK
1
2
3PRODQLTYQTYPRICEDISCPRICEAFTERDISCNETAMOUNTVolPerPieceTotalVolume
422x22x4FS10035750178.5178501,936193600
522x18x3D1501775088.5132751,188178200
678x60x5FSP252300023005750023,400585000
7GaoBQ25125012531252,00050000
8PillowBQ1007007070001,700170000
9
10
11TotalAmount98,7501,176,800
12
13TotalVolume1,176,800
14
Sheet1


i have a price list in another sheet . I want that i write Product ,Quality and Quantity the Rest of things are picked up from the Price List in another sheet of the same workbook. The Discount is also mentioned along with the Name of Quality Ill show u in the next Post
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

TheBuGz

Spammer
Joined
Jan 25, 2004
Messages
367
The invoice is on sheet two of the same file and looks something like this as shown here can any one help me out how to make That Invoice working The maximum Entries in That Invoice are 50 in Product Column
Order.xls
ABCDEFG
2
3PRODFS=50DEC=50EAG=57BQ=0
422x22x4357288245
522x22x3269217183
622x22x3180144122
722x18x3227177151
818x18x3185144122
9Gao125
10Pillow70
11
Sheet2
 

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
Hi TheBuGZ:

The layout in sheet2 is not that of an Invoice -- rather the layout in Sheet1 is more like an invoice.

Please define the various terms what they mean and what these have to do with pricing the item, and please make your question very specific. You may know exactly what you are trying to accomplish but the viewers can only go by what you clearly describe on the board.
 

TheBuGz

Spammer
Joined
Jan 25, 2004
Messages
367
well sheet 2 is actually a price list i want Invoice to get values from that price list

I just had to enter size

Product Quality Quantity
22x22x4 FS 50



The Rest thing Comes up by itself the discount and all the things further come up from the source i.e Price List


Plz Help me!!!!
 

nbrcrunch

Well-known Member
Joined
Jan 1, 2003
Messages
2,062

ADVERTISEMENT

Read the help file on the VLOOKUP function. Also read the SUMIF function.
 

TheBuGz

Spammer
Joined
Jan 25, 2004
Messages
367
I surely am not that SMARTER or CAPABLE than what u have just thoughtof me.
If i could have done it on my own why would i be here giving u ppl trouble.
 

nbrcrunch

Well-known Member
Joined
Jan 1, 2003
Messages
2,062

ADVERTISEMENT

I certainly apologize if my brief note came across as abrupt or rude. Nothing like that was intended.

Nonetheless, the VLOOKUP function is not that hard to decipher. Based on a lookup value, in your case a cell containing a code number, it can return another cell's value, in your case a unit price. I have used it in applications such as:

I get a online report from my phone company of my monthly transactions. I download it, sort it by called-number and then use COUNTIF, SUMIF and VLOOKUP functions to give me counts of calls to each number, total costs to each number and other important info.

If after comtemplating those functions you still need more tutoring, please feel free to indicate that.
 

just_jon

Legend
Joined
Sep 3, 2002
Messages
10,473
Book1
BCDEFGHIJ
2QualitySize1st2nd3rd4th5thMix
3Discount50525762670
4Product22x22x4357288245176135Gao125
522x22x3269217183165120Pillow70
622x22x2185144122111103Sides60
722x18x3227177151134118Head80
818x18x3185144122111101Bottom65
Sheet2
Book1
BCDEFGHIJ
3ProductQualQtyU/PDisc.NetU/PTotalNetPriceVolPerPieceTotalVolume
422x22x41st12535750%178.5022,312.501,936242,000
522x22x32nd17521752%104.1618,228.001,452254,100
622x18x33rd7515157%64.934,869.751,18889,100
718x18x35th12510167%33.334,166.25972121,500
822x22x25th7510367%33.992,549.2596872,600
9GaoMix251250%125.003,125.002,00050,000
10PillowMix100700%70.007,000.007,000700,000
Sheet1

Formulas:
E4: =IF(ISNUMBER(--LEFT(B4,1)),VLOOKUP(B4,Sheet2!C$4:H$8,MATCH(C4,Sheet2!C$2:H$2,0),0),VLOOKUP(B4,Sheet2!I$4:J$8,2,0))
F4: =INDEX(Sheet2!D$3:I$3,0,MATCH(C4,Sheet2!D$2:I$2,0))/100
G4: =E4-E4*F4
H4: =D4*G4
I4: =IF(B4="Pillow",D4*G4,EVAL("="&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B4,"Gao",2000),"x","*"),"mm","/25.4")))
J4: =D4*I4
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,101
Messages
5,768,101
Members
425,453
Latest member
bince

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top