How can I use vlookups, or other methods, to filter data based on numerous drop down lists?

[Jomow]

Hi Everyone,


I have been given a project to create a report that any user can use to easily see my company's sale, cost of sales, orderbook etc for a specific item, month and year.


I want the master tab to be the only tab the user will use and want them to have 3 drop-down menus, one with the item, one with the month and one with the year, then a 4th box to show the sales for that criteria. All the database will be in another tab which feeds data to the master tab.


For this example I have just used sales to see if this process can actually be done.


Below is what I want the master tab to look like and also a sample of what the sales would look like. They are all made up as I cannot actually show my company's sales as it is sensitive but they can easily replace the sample. The layout can also be changed to make it easier if need be.


If any of you excel wizards have any suggestions or solutions then that would be greatly appreciated.


Regards,
Jonny.

Excel 2010

	A	B	C	D	E	F
1
2		The Colour Company
3
4		Item	Month	Year	Sales
5
6

<tbody>
</tbody>

Master Sheet​

B5, C5 and D5 are drop down lists based on this:

Excel 2010

	A	B	C	D	E	F	G
1		Item		Month		Year
2		Red		January		2011
3		Blue		February		2012
4		Green		March		2013
5		Yellow		April		2014
6		Orange		May		2015
7		White		June
8		Black		July
9		Brown		August
10		Purple		September
11		Pink		October
12				November
13				December
14				Q1
15				Q2
16				Q3
17				Q4
18

<tbody>
</tbody>

Lookup List​

This is the sample data:

Sales 2011	Item	January	February	March	April	May	June	July	August	September	October	November	December	Q1	Q2	Q3	Q4
	Red	8	6	2	5	8	2	4	5	6	9	9	4	29	21	26	30
	Blue	2	4	7	4	6	7	2	8	8	9	6	3	28	18	16	14
	Green	3	10	8	9	6	1	9	1	6	2	7	10	35	17	17	37
	Yellow	10	8	10	6	8	10	2	7	6	6	3	10	10	21	31	25
	Orange	9	1	1	9	5	8	10	7	1	1	5	5	36	15	12	24
	White	7	6	6	3	2	9	9	6	10	1	9	1	20	30	28	37
	Black	8	3	4	10	3	7	1	10	6	7	5	1	36	22	25	33
	Brown	4	5	7	2	1	7	2	2	8	9	3	10	27	23	24	32
	Purple	2	6	4	5	6	3	2	9	2	8	3	3	29	32	13	22
	Pink	1	4	5	9	1	7	9	2	8	4	7	1	34	39	40	36
Sales 2012	Item	January	February	March	April	May	June	July	August	September	October	November	December	Q1	Q2	Q3	Q4
	Red	6	10	6	2	3	7	3	10	2	3	7	5	26	38	37	37
	Blue	5	4	7	9	4	3	2	2	5	3	8	1	16	18	11	15
	Green	7	5	10	1	10	3	5	8	6	3	1	9	27	12	12	38
	Yellow	4	2	1	3	4	6	10	1	5	4	6	9	11	21	32	20
	Orange	3	5	5	8	3	10	3	1	8	3	1	5	20	25	28	31
	White	4	2	8	5	1	2	7	7	8	9	3	7	11	12	27	13
	Black	1	6	3	2	10	4	6	7	3	8	5	7	34	31	33	10
	Brown	7	7	2	3	4	4	6	4	10	9	10	9	15	36	12	16
	Purple	10	3	8	3	2	3	7	10	6	8	6	1	38	36	22	26
	Pink	2	2	8	7	7	8	1	8	7	1	5	5	31	40	39	15
Sales 2013	Item	January	February	March	April	May	June	July	August	September	October	November	December	Q1	Q2	Q3	Q4
	Red	9	2	6	2	8	2	2	8	6	7	3	4	20	40	28	32
	Blue	9	4	3	5	5	3	1	5	1	7	10	3	18	27	29	26
	Green	8	3	9	8	1	2	4	7	8	8	1	3	38	18	12	20
	Yellow	8	7	8	10	2	9	9	5	3	4	4	1	40	28	31	18
	Orange	5	9	2	10	9	9	6	3	10	5	8	7	21	29	19	25
	White	3	10	7	3	2	6	10	3	2	9	3	9	24	22	36	13
	Black	3	6	4	10	6	1	6	3	10	5	7	1	38	12	24	23
	Brown	2	8	1	1	8	8	6	4	1	1	2	5	33	10	20	18
	Purple	6	5	3	8	3	2	2	10	1	8	6	10	20	13	26	34
	Pink	1	10	5	10	5	3	10	1	8	10	2	9	15	19	34	35
Sales 2014	Item	January	February	March	April	May	June	July	August	September	October	November	December	Q1	Q2	Q3	Q4
	Red	8	5	6	8	8	9	1	1	2	6	5	1	24	27	14	29
	Blue	9	5	4	2	9	5	9	6	7	7	4	5	23	12	16	27
	Green	1	3	9	5	2	10	3	9	6	2	10	1	17	32	38	30
	Yellow	4	3	4	1	2	3	2	4	9	9	1	2	18	21	19	14
	Orange	6	6	7	6	3	8	3	6	1	6	7	5	40	18	40	15
	White	10	1	7	4	2	7	10	1	2	10	8	4	18	29	12	18
	Black	8	4	6	8	3	3	3	2	8	7	4	4	13	15	14	13
	Brown	2	9	8	8	3	4	5	2	6	4	1	6	28	26	11	22
	Purple	6	3	7	2	10	6	9	4	6	2	5	4	24	28	27	32
	Pink	8	9	6	3	9	1	9	6	7	8	2	5	22	17	13	37
Sales 2015	Item	January	February	March	April	May	June	July	August	September	October	November	December	Q1	Q2	Q3	Q4
	Red	10	3	10	6	5	9	7	6	8	5	5	10	11	28	15	25
	Blue	3	6	6	2	5	4	6	3	4	3	10	4	40	39	16	32
	Green	9	9	4	1	1	6	3	8	7	6	7	4	32	12	29	39
	Yellow	9	10	10	3	4	9	6	5	9	3	2	10	12	30	21	22
	Orange	5	9	4	1	3	7	4	8	9	6	4	2	35	39	31	21
	White	1	3	3	9	7	1	3	6	10	3	4	8	22	35	11	19
	Black	8	6	4	9	1	2	10	7	7	2	4	9	13	25	37	27
	Brown	3	4	7	4	9	5	4	3	2	9	3	5	26	32	32	17
	Purple	6	7	2	3	9	2	9	7	4	2	10	9	33	38	32	13
	Pink	5	3	4	1	9	6	9	3	4	6	7	10	10	32	25	21

<tbody>
</tbody>

 

[Oeldere]

A pivot table is also possible?

 

[Jomow]

A pivot table is also possible?

Thanks for your response but sorry I cannot use a pivot table, it has to be an actual cell with the data specified by the criteria the user chooses.

 

[Oeldere]

In case you change your mind (a pivot table).

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Year	Sales 2015
2
3	Som van value	Kolomlabels
4	Rijlabels	1	2	3	4	5	6	7	8	9	10	11	12
5	Black	8	6	4	9	1	2	10	7	7	2	4	9
6	Blue	3	6	6	2	5	4	6	3	4	3	10	4
7	Brown	3	4	7	4	9	5	4	3	2	9	3	5
8	Green	9	9	4	1	1	6	3	8	7	6	7	4
9	Orange	5	9	4	1	3	7	4	8	9	6	4	2
10	Pink	5	3	4	1	9	6	9	3
11	Purple	6	7	2	3	9	2	9	7	4	2	10	9
12	Red	10	3	10	6	5	9	7	6	8	5	5	10
13	White	1	3	3	9	7	1	3	6	10	3	4	8
14	Yellow	9	10	10	3	4	9	6	5	9	3	2	10
15	Eindtotaal	59	60	54	39	53	51	61	56	60	39	49	61
Blad5

 

[Jomow]

Does anyone else have any suggestions? Is this even possible?


Jonny.

 

[Oeldere]

Yes I have, with an pivot table, it is possible.

The user can change the (result) of the pivot table to his / her need.

You could hide the database that will fill the pivot table.