# Logic issues

#### chrisguk

##### Board Regular
Normally I have no problem trying to understand my own logic for a formula but this time I seem to of hit a brick wall:

This is what I am trying to achieve:

SHEET 1

 DROPDOWN LIST WITH MONTHS (CELL A1)

<tbody>
</tbody>

 DROPDOWN LIST WITH YEARS (CELL B1)

<tbody>
</tbody>

EMPTY TABLE WITH FORMULAS
 A1 B1 C1 ITEM Price Qty

<tbody>
</tbody>

SHEET 2

 A1 B1 C1 D1 E1 ITEM Price Qty Month Year Brush 2.00 3 Jan 2016 Handle 1.00 4 Feb 2016 Cloth 1.00 1 Jan 2016

<tbody>
</tbody>

So to populate the table in Sheet 1 I want to look in the whole of sheet 2 by stating list all items that match the criteria selected in the dropdown lists for example:

Select Jan 2016: It will therefore populate the table dynamically (expanding the rows) with data from sheet 2. I know I can achieve the same result with filters on sheet 2 but it was necessary for cosmetic reasons to have a separate sheet with this data.

I have tried to achieve this with IF(OR SUMIFS and VLOOKUP but seem to be going around in circles.

I would be very grateful if anyone is able to help me with this.

Many thanks
Chris

### Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

#### AliGW

##### Banned
The formula can be copied across and down:

Excel 2016 (Windows) 32 bit
ABC
3Jan
4
52016
6
7
8EMPTY TABLE WITH FORMULAS
9A1B1C1
10Brush23
11Cloth11
Sheet1
Cell Formulas
RangeFormula
A10{=IFERROR(INDEX(Sheet2!A\$1:A\$14,SMALL(IF((Sheet2!\$D\$5:\$D\$14=Sheet1!\$A\$3)*(Sheet2!\$E\$5:\$E\$14=Sheet1!\$A\$5),ROW(Sheet2!\$A\$5:\$A\$14)),ROW(A1))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

##### MrExcel MVP
A:E of Sheet2 houses the data.

Sheet1

 Jan 2016 2 idx ITEM Price Qty Month Year 1 Brush 2 3 Jan 2016 3 Cloth 1 1 Jan 2016

<tbody>
</tbody>

A1:B1 shows the user choices for month and year.

In A2 just enter:

=COUNTIFS(Sheet2!\$D\$2:\$D\$4,\$A\$1,Sheet2!\$E\$2:\$E\$4,\$B\$1)

In A4 control+shift+enter, not just enter, and copy down:

=IF(ROWS(\$A\$4:A4)>\$A\$2,"",SMALL(IF(Sheet2!\$D\$2:\$D\$4=\$A\$1,IF(Sheet2!\$E\$2:\$E\$4=\$B\$1,ROW(Sheet2!\$A\$2:\$A\$4)-ROW(Sheet2!\$A\$2)+1)),ROWS(\$A\$4:A4)))

In B4 just enter, copy across, and down:

=IF(\$A4="","",INDEX(Sheet2!A\$2:A\$4,\$A4))

#### chrisguk

##### Board Regular
A:E of Sheet2 houses the data.

Sheet1

 Jan 2016 2 idx ITEM Price Qty Month Year 1 Brush 2 3 Jan 2016 3 Cloth 1 1 Jan 2016

<tbody>
</tbody>

A1:B1 shows the user choices for month and year.

In A2 just enter:

=COUNTIFS(Sheet2!\$D\$2:\$D\$4,\$A\$1,Sheet2!\$E\$2:\$E\$4,\$B\$1)

In A4 control+shift+enter, not just enter, and copy down:

=IF(ROWS(\$A\$4:A4)>\$A\$2,"",SMALL(IF(Sheet2!\$D\$2:\$D\$4=\$A\$1,IF(Sheet2!\$E\$2:\$E\$4=\$B\$1,ROW(Sheet2!\$A\$2:\$A\$4)-ROW(Sheet2!\$A\$2)+1)),ROWS(\$A\$4:A4)))

In B4 just enter, copy across, and down:

=IF(\$A4="","",INDEX(Sheet2!A\$2:A\$4,\$A4))

Your solution works perfect thank you so much for your assistance. Because I had to change some of the references in the production spreadsheet it enabled me to learn and understand the formula.

##### MrExcel MVP
Your solution works perfect thank you so much for your assistance. Because I had to change some of the references in the production spreadsheet it enabled me to learn and understand the formula.

That's great. You are welcome.

Replies
3
Views
762
Replies
2
Views
980
Replies
8
Views
334
Replies
24
Views
689
Replies
4
Views
170

1,196,017
Messages
6,012,872
Members
441,737
Latest member
bijayche

### 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?

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