Invoice in Excel

[TheBuGz]

Here is what i am trying to make an Invoice like this

Order.xls
	A	B	C	D	E	F	G	H	I	J	K
1
2
3		PROD	QLTY	QTY	PRICE	DISC	PRICEAFTERDISC	NETAMOUNT		VolPerPiece	TotalVolume
4		22x22x4	FS	100	357	50	178.5	17850		1,936	193600
5		22x18x3	D	150	177	50	88.5	13275		1,188	178200
6		78x60x5	FSP	25	2300	0	2300	57500		23,400	585000
7		Gao	BQ	25	125	0	125	3125		2,000	50000
8		Pillow	BQ	100	70	0	70	7000		1,700	170000
9
10
11							TotalAmount	98,750			1,176,800
12
13							TotalVolume	1,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

 

[TheBuGz]

Thanx a lot just_jon.


U really bailed me out of the problem.


U really are Great ray:

 

[TheBuGz]

Well Thanx for Helping I just want to continue and enhance some more Features into the invoice

Order.xls
	B	C	D	E	F	G	H	I	J
6	PRODUCT	Quality	Quantity	Price	Disc.	AfterDisc.	NetAmt	VolPerPc	TotalVolume
7	78x66x4	1st	10	4,652	0.50	2,326	23,260	20,592	205,920
8	78x72x5	2nd	10	5,009	0.50	2,505	25,045	28,080	280,800
9	78x72x5	3rd	15	4,114	0.50	2,057	30,855	28,080	421,200
10	22x22x3	1st	20	269	0.50	135	2,690	1,452	29,040
11	22x22x2	2nd	30	144	0.50	72	2,160	968	29,040
12	22x18x3	3rd	40	151	0.50	76	3,020	1,188	47,520
13	22x22x2	4th	50	180	0.57	77	3,870	968	48,400
Invoice

Can i make a list as if i write in Product Column just 78x and the rest of the list is shown up
78x78
78x72
78x66

and if i choose 78x78x
then this sort of list is shown
78x78x6
78x78x5
78x78x4


Guide me plz wat to do

 

[TheBuGz]

Still awaiting Excel Gurus to my reply

 

[mianmazher]

Hi TheBugz

You mean to say a dropdownlist

If a user types for eg 78x in the size column all sizes starting with 78x displays

if user types for eg 72x all sizes starting with 72x displayes

I think this can be done with the help of VBA

ask VBA gurus for help


and me sure they will help u soon when awake (might be sleeping due to time difference in the countries )


Regards


Mian Mazher

 

[TheBuGz]

Thanx Mian Mazher!
Well Waiting For Gurus To Reply to this post

 

[bosco_yip]

Book1
	A	B	C	D	E	F
1	Size	7		criteria:	75x23
2	73x23x34	0		sortoflist:	75x23x36
3	75x25x35	0			75x23x37
4	75x23x36	1			75x23x38
5	75x23x37	2			75x23x40
6	75x23x38	3			75x23x41
7	76x23x39	3			75x23x44
8	75x23x40	4			75x23x45
9	75x23x41	5
10	77x23x42	5
11	76x23x43	5
12	75x23x44	6
13	75x23x45	7
14	78x23x46	7
15
Sheet1

Assume your data on A2:A14

In cell B1 enter :

=COUNTIF(A2:A14,$E$1&"*")

In cell B2 enter :

=COUNTIF(A$2:A2,$E$1&"*")

and drag down to B14

In cell E2 enter :

=IF(COUNTA(E$1:E1)<=$B$1,INDEX($A$2:$A$14,MATCH(ROW()-ROW(A$2)+1,$B$2:$B$14,0)),"")

and drag down to E14

Enter your criteria in cell E1 ( in this example : 75x23 )


Hope that helps

Regards

 

[TheBuGz]

bosco_yip
Thanx for ur help but i did not asked for this i want some vba addin as like to make that thing because i have a very long list in which there are different sizes appended here under

Order.xls
	C	D	E	F
5	22x18x3	227	177	151
6	22x22x2	180	144	122
7	22x22x3	269	217	183
8	22x22x4	357	288	245
9	72x30x6	2420	2135	1650
10	72x36x0.5	247	209	164
11	72x36x1	494	419	330
12	72x36x1.5	742	628	493
13	72x36x12	6169	5024	3938
14	72x36x2	989	837	656
15	72x36x3	1990	1685	1396
16	72x36x4	2399	2105	1717
17	72x36x5	3003	2520	2049
18	72x36x6	3554	2924	2381
19	72x39x3	2116	1796	1477
20	72x39x4	2557	2242	1830
21	72x42x3	2242	1895	1557
22	72x42x4	2720	2378	1937
23	72x48x12	8218	6709	5259
24	72x48x6	4200	3140	2605
25	72x54x6	5160	4730	3600
26	72x60x4	3825	3310	2670
27	72x60x6	5735	4681	3760
28	72x72x4	4673	3901	3167
29	72x72x5	5765	4725	3831
30	72x72x6	6794	5555	4494
Price List

 

[TheBuGz]

like if i write 72x36x


i get a list like this or something similar to this list

Order.xls
	A	B	C	D
1	SIZE
2
3	72x36x0.5
4	72x36x1
5	72x36x1.5
6	72x36x12
7	72x36x2
8	72x36x3
9	72x36x4
10	72x36x5
11	72x36x6
Sheet2

 

[TheBuGz]

Still awaiting Reply From Gurus Here