How to do this, what formula??? or what VBA Codes???

[doms123]

I am using Excel2007, i'm doing database as my project and i am having a problem on how to do this...


I have have three sheets first is the input sheet...

Excel Workbook
	A	B	C	D	E	F
1	*	*	*	*	*	*
2	*	poducts	*			*
3	*	*	*	*	*	*
4	*	date delivered	*	*	*	*
5	*	*	month	Date	Year	*
6	*	location of delivery	*			*
7	*	*	*	*	*	*
8	*	delivered by	*			*
9	*	*	*	*	*	*
10	*	sales	*			*
11	*	*	*	*	*	*
12	*	*	Encode		*	*
13	*	*	*	*	*	*
Sheet1

then my masterlist....

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	Products	Date Delivered			Location of Delivery	Delivered by	Sales	*	*
2		Month	Day	Year				*	DELETE
3	Shoes1	June	8	2010	Area 1	Boy1	1k	*	button
4	Shoes2	December	17	2010	Area2	Boy2	1k	*	*
5	Shoes3	August	7	2010	Area 3	Boy3	1k	*	*
6	Undrewears1	September	5	2010	Area 5	Boy3	1k	*	*
7	Undrewears2	October	5	2010	Area 5	Boy2	1k	*	*
8	Undrewears3	November	16	2010	Area 2	Boy3	1k	*	*
9	Undrewears4	December	5	2010	Area 3	Boy3	1k	*	*
10	Undrewears5	June	5	2012	Area 2	Boy1	1k	*	*
11	Sandals1	September	25	2010	Area 1	Boy3	1k	*	*
12	Sandals2	October	25	2010	Area 2	Boy4	1k	*	*
13	Sandals3	November	25	2010	Area 3	Boy2	1k	*	*
14	Towels1	December	22	2010	Area 4	Boy1	1k	*	*
15	Towels2	January	25	2010	Area 5	Boy4	1k	*	*
16	Towels3	June	5	2010	Area 2	Boy4	1k	*	*
17	Towels4	May	7	2011	Area 1	Boy1	1k	*	*
18	Towels5	June	5	2010	Area 2	Boy2	1k	*	*
19	Towels6	August	21	2010	Area2	Boy3	1k	*	*
20	Towels7	June	9	2011	Area 4	Boy4	1k	*	*
21	Towels7	July	14	2011	Area 5	Boy1	1k	*	*
22	Shorts1	August	9	2011	Area 1	Boy2	1k	*	*
23	Shorts2	June	14	2011	Area 5	Boy1	1k	*	*
24	*	*	*	*	*	*	*	*	*
25	*	*	*	*	*	*	*	*	*
26	*	*	*	*	*	*	*	*	*
27	*	*	*	*	*	*	*	*	*
Sheet2

and my problem is this is my third sheet this is a summary or masterlist report on Per Location of Deliveries. I want to do like this

Excel Workbook
	A	B	C	D	E	F	G
1	Location of Delivery	Area 2
2	Products	Date Delivered			Delivered by	Sales
3		Month	Day	Year
4	Shoes2	December	17	2010	Boy2	1k
5	Undrewears3	November	16	2010	Boy3	1k
6	Undrewears5	June	5	2012	Boy1	1k
7	Sandals2	October	25	2010	Boy4	1k
8	Towels3	June	5	2010	Boy4	1k
9	Towels5	June	5	2010	Boy2	1k
10	Towels6	August	21	2010	Boy3	1k
11							Area 1
12							Area 2
13							Area 3
14							Area 4
15							Area 5
Sheet3

the B1 is the list box of areas where it delivers, from Area 1 to Area 5 if I choose Area 2 the details that with Area 2 will appear same on the other areas...


then if i delete an entry on the masterlist it will be also delete on the summary per area delivered list...

Please Help me...
Thank you in advance

 

[Akashwani]

Hi doms123,

Is this what you want on sheet3?.....

Excel Workbook
	A	B	C	D	E	F	G	H
1	Location of Delivery	Area 2					7	*
2	Products	Date Delivered			Delivered by	Sales	*	Area 1
3		Month	Day	Year			*	Area 2
4	Shoes2	December	17	2010	Boy2	1k	*	Area 3
5	Undrewears3	November	16	2010	Boy3	1k	*	Area 4
6	Undrewears5	June	5	2012	Boy1	1k	*	Area 5
7	Sandals2	October	25	2010	Boy4	1k	*	*
8	Towels3	June	5	2010	Boy4	1k	*	*
9	Towels5	June	5	2010	Boy2	1k	*	*
10	Towels6	August	21	2010	Boy3	1k	*	*
11	*	*	*	*	*	*	*	*
12	*	*	*	*	*	*	*	*
13	*	*	*	*	*	*	*	*
14	*	*	*	*	*	*	*	*
15	*	*	*	*	*	*	*	*
Sheet3

Copy the formulas in Row4 down as far as you require.

Good luck.

Ak

 

[doms123]

Thank you sir but I think you need to elaborate more of that...

 

[doms123]

Please elaborate more, I;m having trouble with the brackets {}...

 

[Akashwani]

Hi,

You need to type in the formula without {} then press ctrl shift enter NOT just enter.

Have a look at this....


doms123.xls


Good luck

Ak

 

[doms123]

Thank you man... You are an expert

 

[doms123]

What's the pivot table for????

 

[Akashwani]

HI,

The Pivot table is just to show you another way of achieving your objective.
You can delete that sheet if you do not require it, or you can use it as a sample and play around with it to see the different ways that you can manipulate the data.

Have fun.

Ak

 

[doms123]

ok. thanks man for the help... you're a big help...

 

[doms123]

Sir I'm having with this last part of the formula,

ROW(Sheet1!$A$3)+1),"")}

because i am not using this formula on this sheet, this is only an example. I am using the idea on my own project, please help me...