Logic issues

chrisguk

Board Regular
Joined
Jan 10, 2011
Messages
135
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
A1B1C1
ITEMPriceQty

<tbody>
</tbody>

SHEET 2

A1B1C1D1E1
ITEMPriceQtyMonthYear
Brush2.003Jan2016
Handle1.004Feb2016
Cloth1.001Jan2016

<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

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
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.
 
Upvote 0
A:E of Sheet2 houses the data.

Sheet1

Jan2016
2
idxITEMPriceQtyMonthYear
1Brush23Jan2016
3Cloth11Jan2016

<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))
 
Upvote 0
A:E of Sheet2 houses the data.

Sheet1

Jan2016
2
idxITEMPriceQtyMonthYear
1Brush23Jan2016
3Cloth11Jan2016

<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.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,202
Members
448,554
Latest member
Gleisner2

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?

Disable AdBlock

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
Back
Top