Multiple criterias and a date range....

[pingvin]

Sheet 1	A	B	C	D	E	F
1	Category:	Drop down list "Category"	Year:	Drop down list "Year"	Month:	Drop down list "Month"
2
3		Art code	Price	Menu name
4 examples		89865	1590	Pizza
5 examples		15684	8990	Mushroom soup
6 examples		64791	4590	Garlic toast
7 examples		95548	3990	Hamburger
8
9

<tbody>
</tbody>

Sheet 2	A	B	C	D	E	F
1	Art code	Price	Menu name	Range category	Start date	End date
2	89865	1590	Pizza	Main dishes	20180125	20180506
3	54453	2990	Spaghetti	Main dishes	20180201	20180831
4	15684	8990	Mushroom Soup	Main dishes	20180401	20180930
5	64791	4590	Garlic toast	Main dishes	20180507	20181231
6	95548	3990	Hamburger	Main dishes	20180801	20181231
7	23548	9990	Lasagna	Main dishes	20180601	20181031
8	19865	159	Prociutto	Cold starters	20180125	20180506
9	24453	299	Marinated olives	Cold starters	20180201	20180831
10	55684	899	Open faced sandwich	Cold starters	20180401	20180831
11	74791	459	Ceasar salad	Salads	20180507	20181231
12	25548	399	Chorizo	Add-ons	20180801	20181231
13	83548	999	Chocolate mousse	Desserts	20180601	20181031

<tbody>
</tbody>

I have a workbook with two sheets.
On sheet 1 I have 3 drop down list where I for example select "Main dishes", "2018" and "March".
Based on these criterias I would like to list the matching criterias from sheet 2 to be listed on sheet 1 in cell B4:D9.

In this case since I have selected March in drop down menu on sheet 1. I would like Main dishes with start date and end date between March to be listed.
I have tried index and match but I'm not sure how to acomplish this with having multiple criterias and a date range.... This problem is driving me crazy. If possible could this be achieved without an array formula or without VBA.

 

[AhoyNC]

Maybe something like this.
In your example it doesn't look like either Garlic toast or Hamburger where in the 3rd month.

The formula in A4 of the example (ROW ID) is an array formula and must be entered with CTRL-SHIFT-ENTER. Then drag down column.
Formula in B4 can just be copied across and down as needed.

Excel Workbook
	A	B	C	D	E	F
1	Category:	Main dishes	Year:	2018	Month:	3
2
3	Row ID	Art code	Price	Menu name
4	1	89865	1590	Pizza
5	2	54453	2990	Spaghetti
Sheet1

Excel Workbook
	A	B	C	D	E	F
1	Art code	Price	Menu name	Range category	Start date	End date
2	89865	1590	Pizza	Main dishes	1/25/2018	5/6/2018
3	54453	2990	Spaghetti	Main dishes	2/1/2018	8/31/2018
4	15684	8990	Mushroom Soup	Main dishes	4/1/2018	9/30/2018
5	64791	4590	Garlic toast	Main dishes	5/7/2018	12/31/2018
6	95548	3990	Hamburger	Main dishes	8/1/2018	12/31/2018
7	23548	9990	Lasagna	Main dishes	6/1/2018	10/31/2018
8	19865	159	Prociutto	Cold starters	1/25/2018	5/6/2018
9	24453	299	Marinated olives	Cold starters	2/1/2018	8/31/2018
10	55684	899	Open faced sandwich	Cold starters	4/1/2018	8/31/2018
11	74791	459	Ceasar salad	Salads	5/7/2018	12/31/2018
12	25548	399	Chorizo	Add-ons	8/1/2018	12/31/2018
13	83548	999	Chocolate mousse	Desserts	6/1/2018	10/31/2018
Sheet2

 

[pingvin]

Thanks for you help. However I can't get it to work. When I paste the formula to cell A4 in sheet 1 and press CTRL+SHIFT+ENTER a dialog box pop-ups and asks for "Updates Values: 2". What am I doing wrong?

 

[AhoyNC]

It looks like in this part of the formula a space got added between the Sheet and 2. It should be Sheet2!

IF(MONTH(Sheet 2!$E$2:$E$13)

It should also be noted that your dates need to be actual Excel dates. It's not clear in your example if 20180125 is an actual date formatted as "yyymmdd" or just a number or a text field.

It looks like the program I use to post keeps putting a space between the Sheet and 2 when it breaks the formula into two lines, so you will need to remove that space.

Sheet1

	A	B	C	D	E	F
1	Category:	Main dishes	Year:	2018	Month:	3
2
3	Row ID	Art code	Price	Menu name
4	1	89865	1590	Pizza
5	2	54453	2990	Spaghetti

<tbody>
</tbody>

Spreadsheet Formulas

Cell Formula A4 {=IFERROR(SMALL(IF(Sheet2!$D$2:$D$13=$B$1,IF(YEAR(Sheet2!$E$2:$E$13)=$D$1,IF(YEAR(Sheet2!$F$2:$F$13)=$D$1,IF(MONTH(Sheet2!$E$2:$E$13)<=$F$1,IF(MONTH(Sheet2!$F$2:$F$13)>=$F$1,ROW(Sheet2!$D$2:$D$13)-ROW(Sheet2!$D$2)+1))))),ROWS($A$4:A4)),"")} B4 =IF($A4="","",INDEX(Sheet2!$A$2:$C$13,$A4,MATCH(B$3,Sheet2!$A$1:$C$1,0))) C4 =IF($A4="","",INDEX(Sheet2!$A$2:$C$13,$A4,MATCH(C$3,Sheet2!$A$1:$C$1,0))) D4 =IF($A4="","",INDEX(Sheet2!$A$2:$C$13,$A4,MATCH(D$3,Sheet2!$A$1:$C$1,0))) <tbody> </tbody>

Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[pingvin]

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

1

Main dishes

Year:

2018

Month:

3

2

2018

2019

3

Row ID

Art code

Price

Menu name

Aug

Sep

Oct

Nov

Dec

Jan

Feb

Mar

Apr

May

Jun

Jul

4

1

89865

1590

Pizza

5

2

54453

2990

Spaghetti

6

7

8

<tbody>
</tbody>

Thanks a bunch I got it to work however now I have the next problem that I cannot solve.
Cell E2:I2 is merged and contains the formula "=$C$1"
Cell J2:P2 is merged and contains the formula "=$C$1+1"

What I would like to do is to use conditional formatting to for cell E4:P8. If C1 and E1 is between start date and end date on sheet 2 I would like the cells E4:P8 to change color to yellow.

Can this be accomplished without concatenating cell C1 and E1?

Much appreciated for anybody that can solve this for me.

 

[pingvin]

Is there anybody out there that could help me further with conditional formatting?

 

[AhoyNC]

Try:
I don't see the need for merged cells. I would just put your years in E2 & J2, as merged cells can cause issues with formulas. However this should work with your merged cells.

I'm assuming that the months in cells E3:P3 are text and not dates formatted as mmm.

You will need two formulas. One for the year in cell E2 and one for the year in J2.

For 2018 you would highlight cells E4:I8.

-Go to Conditional Formatting
-New Rule
Enter formula shown below in cell E4
-Choose format you want.

Repeat the above for year 2019, but highlight (choose) cells J4:P8.

Excel Workbook
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Year:	2018	Month:	3
2				2018					2019
3	Art code	Price	Menu name	Aug	Sep	Oct	Nov	Dec	Jan	Feb	Mar	Apr	May	Jun	Jul
4	89865	1590	Pizza
5	54453	2990	Spaghetti
6
7
8
Sheet

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E4	1. / Formula is =AND(INDEX(Sheet2!$E$2:$E$13,$A4)<=DATEVALUE(1&E$3&$E$2),INDEX(Sheet2!$F$2:$F$13,$A4)>=DATEVALUE(1&E$3&$E$2))	Abc
J4	1. / Formula is =AND(INDEX(Sheet2!$E$2:$E$13,$A4)<=DATEVALUE(1&E$3&$J$2),INDEX(Sheet2!$F$2:$F$13,$A4)>=DATEVALUE(1&E$3&$J$2))	Abc

 

[pingvin]

Hi,

I can't get the formula to work could it be that "$A4" is not correct?
=AND(INDEX(Sheet2!$E$2:$E$13,$A4)<=DATEVALUE(1&E$3&$E$2),INDEX(Sheet2!$F$2:$F$13,$A4)>=DATEVALUE(1&E$3&$E$2))