Filter using not FILTER but formula

M

makinmomb

Active Member
Joined
Dec 23, 2013
Messages
401
I have the following data on sheet1

COLOURART NO.BRANDSIZESURFACEPACKINGM2 CTNOUR CODEINCSTKBOOKFREE
Grey GCM04CEMENTUM60 X 60Matt31.08239.000.022,450.00 0
White GCM01CEMENTUM60 X 60Matt31.08239.000.012,450.00 0
CremaGGS12GRANTITE STONE30 X 60Rock81.44239.000.033,505.00 0
CremaGGS12GRANTITE STONE30 X 60Rock31.08239.000.782,590.00 0
<colgroup><col width="52" style="width: 39pt; mso-width-source: userset; mso-width-alt: 1901;"> <col width="54" style="width: 41pt; mso-width-source: userset; mso-width-alt: 1974;"> <col width="95" style="width: 71pt; mso-width-source: userset; mso-width-alt: 3474;"> <col width="44" style="width: 33pt; mso-width-source: userset; mso-width-alt: 1609;"> <col width="55" style="width: 41pt; mso-width-source: userset; mso-width-alt: 2011;"> <col width="52" style="width: 39pt; mso-width-source: userset; mso-width-alt: 1901;"> <col width="44" style="width: 33pt; mso-width-source: userset; mso-width-alt: 1609;"> <col width="61" style="width: 46pt; mso-width-source: userset; mso-width-alt: 2230;"> <col width="49" style="width: 37pt; mso-width-source: userset; mso-width-alt: 1792;"> <col width="27" style="width: 20pt; mso-width-source: userset; mso-width-alt: 987;"> <col width="37" style="width: 28pt; mso-width-source: userset; mso-width-alt: 1353;"> <col width="32" style="width: 24pt; mso-width-source: userset; mso-width-alt: 1170;"> <tbody> </tbody>

Can a drop box on sheet 2 specifying tile size pull data or without drop box , I know the normal filter is the best option but trying to spoon feed the sales team since some are weak users, they would hardly know how to use the FILTER option
 
Sort by date Sort by votes

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
While I enjoy the program with following formula working like a ROCKET , if I were to add a third drop box that would allow allow displaying specific quantity e.g. 250 to 500 and 500 to 750 and 750 to 1000 and 1000 above and ALL

=IFERROR(INDEX(data,SMALL(IF(INDEX(data,,MATCH($B$1,Sheet1!$A$1:$L$1,0))=$A$1,IF(INDEX(data,,MATCH($B$2,Sheet1!$A$1:$L$1,0))=$A$2,ROW(data)-ROW(INDEX(data,1,1))+1)),ROWS($A$5:$A5)),COLUMNS($A$5:A$5)),"")

20 X 90SIZE
MattSURFACE
COLOURART NO.BRANDSIZESURFPACKM2 CTNOUR CODEINCSTKBOOKFREE
PineGNW21WOOD20 X 90Matt61.08239.000.14X729.17574155
BeechGNW22WOOD20 X 90Matt61.08239.000.15X910.67500411
EbonyGNW23WOOD20 X 90Matt61.08239.000.16X439.330439
<colgroup><col width="50" style="width: 38pt; mso-width-source: userset; mso-width-alt: 1828;"> <col width="55" style="width: 41pt; mso-width-source: userset; mso-width-alt: 2011;"> <col width="43" style="width: 32pt; mso-width-source: userset; mso-width-alt: 1572;"> <col width="56" style="width: 42pt; mso-width-source: userset; mso-width-alt: 2048;"> <col width="35" style="width: 26pt; mso-width-source: userset; mso-width-alt: 1280;" span="2"> <col width="44" style="width: 33pt; mso-width-source: userset; mso-width-alt: 1609;"> <col width="77" style="width: 58pt; mso-width-source: userset; mso-width-alt: 2816;"> <col width="35" style="width: 26pt; mso-width-source: userset; mso-width-alt: 1280;"> <col width="49" style="width: 37pt; mso-width-source: userset; mso-width-alt: 1792;"> <col width="37" style="width: 28pt; mso-width-source: userset; mso-width-alt: 1353;"> <col width="32" style="width: 24pt; mso-width-source: userset; mso-width-alt: 1170;"> <tbody> </tbody>
 
Upvote 0
FILTER is definitely the best option but I am dealing with
a team that is not EXCEL conversant thus I am forced to spoon feed
 
Upvote 0
makinmomb said:
While I enjoy the program with following formula working like a ROCKET , if I were to add a third drop box that would allow allow displaying specific quantity e.g. 250 to 500 and 500 to 750 and 750 to 1000 and 1000 above and ALL

=IFERROR(INDEX(data,SMALL(IF(INDEX(data,,MATCH($B$1,Sheet1!$A$1:$L$1,0))=$A$1,IF(INDEX(data,,MATCH($B$2,Sheet1!$A$1:$L$1,0))=$A$2,ROW(data)-ROW(INDEX(data,1,1))+1)),ROWS($A$5:$A5)),COLUMNS($A$5:A$5)),"")
Click to expand...

Try this:

Layout

20 X 90SIZESheet2
GripSURFACE
500 to 750STK
*
COLOURART NO.
Grey GCM04
*
*
***************************

<tbody>
</tbody>


Formula

Code: 
In A6 - use Ctrl+Shift+Enter and not only Enter to enter the formula

=IFERROR(INDEX(Data,SMALL(IF(IF($A$1="",$A$1,INDEX(Data,,MATCH($B$1,Sheet1!$1:$1,0)))=$A$1,
IF(IF($A$2="",$A$2,INDEX(Data,,MATCH($B$2,Sheet1!$1:$1,0)))=$A$2,
IF(IF(IFERROR(--LEFT($A$3,4),250),INDEX(Data,,MATCH($B$3,Sheet1!$1:$1,0)),250)>=IFERROR(--LEFT($A$3,4),250),
IF(IF(IFERROR(--RIGHT($A$3,4),250),INDEX(Data,,MATCH($B$3,Sheet1!$1:$1,0)),250) < IFERROR(--RIGHT($A$3,4),99^99),
ROW(Data)-ROW(INDEX(Data,1,1))+1)))),ROWS($A$6:$A6)),COLUMNS($A$6:A$6)),"")

Markmzz</iferror(--right($a$3,4),99^99),
 
Upvote 0
markmzz said:
Try this:

Layout

20 X 90SIZESheet2
GripSURFACE
500 to 750STK
*
COLOURART NO.
Grey GCM04
*
*
***************************

<tbody>
</tbody>


Formula

Code: 
In A6 - use Ctrl+Shift+Enter and not only Enter to enter the formula

=IFERROR(INDEX(Data,SMALL(IF(IF($A$1="",$A$1,INDEX(Data,,MATCH($B$1,Sheet1!$1:$1,0)))=$A$1,
IF(IF($A$2="",$A$2,INDEX(Data,,MATCH($B$2,Sheet1!$1:$1,0)))=$A$2,
IF(IF(IFERROR(--LEFT($A$3,4),250),INDEX(Data,,MATCH($B$3,Sheet1!$1:$1,0)),250)>=IFERROR(--LEFT($A$3,4),250),
IF(IF(IFERROR(--RIGHT($A$3,4),250),INDEX(Data,,MATCH($B$3,Sheet1!$1:$1,0)),250) < IFERROR(--RIGHT($A$3,4),99^99),
ROW(Data)-ROW(INDEX(Data,1,1))+1)))),ROWS($A$6:$A6)),COLUMNS($A$6:A$6)),"")

Markmzz


It says

" the specified formula cannot be entered because it uses more level of nesting that are allowed in the current file format "
Click to expand...
 
Upvote 0
I was wondering if I can save as the first working program and use the second program
for SIZE and QTY , SURFACE can be removed , then what would be the formula on A5
and not A6 since we deleted one row
 
Upvote 0
makinmomb said:
Markmzz


It says

" the specified formula cannot be entered because it uses more level of nesting that are allowed in the current file format "
Click to expand...

What is your version of Excel?

My last formula works with Excel 2007, 2010 and 2013 with the respective file format.

Markmzz
 
Upvote 0
Try this:

Code: 
Create this names:

Data    - Refers To: =Sheet1!$A$2:INDEX(Sheet1!$L:$L,MATCH("ZZZZZ",Sheet1!$A:$A),0)

QTY    - Refers To: =Sheet1!$J$2:INDEX(Sheet1!$J:$J,MATCH("ZZZZZ",Sheet1!$A:$A),0)

SIZE    - Refers To: ==Sheet1!$D$2:INDEX(Sheet1!$D:$D,MATCH("ZZZZZ",Sheet1!$A:$A),0)

SURFACE    - Refers To: =Sheet1!$E$2:INDEX(Sheet1!$E:$E,MATCH("ZZZZZ",Sheet1!$A:$A),0)


Formula

[COLOR=#ff0000][B]In A5 [/B][/COLOR]- - use Ctrl+Shift+Enter and not only Enter to enter the formula

For Excel 2003

=IF(SUM((QTY>=IF(ISNUMBER(-LEFT($A$3,4)),--LEFT($A$3,4),250))*(QTY < IF(ISNUMBER(-RIGHT($A$3,4)),--RIGHT($A$3,4),99^99))*
(COUNTIF($A$1,SIZE)+($A$1=""))*(COUNTIF($A$2,SURFACE)+($A$2=""))) < ROWS(A$5:A5),"",
INDEX(Data,SMALL(IF((QTY>=IF(ISNUMBER(-LEFT($A$3,4)),--LEFT($A$3,4),250))*(QTY < IF(ISNUMBER(-RIGHT($A$3,4)),--RIGHT($A$3,4),99^99))*
(COUNTIF($A$1,SIZE)+($A$1=""))*(COUNTIF($A$2,SURFACE)+($A$2="")),ROW(Data)-ROW(INDEX(Data,1,1))+1),ROWS(A$5:A5)),COLUMNS($A5:A5)))

For Excel 2007+

=IFERROR(INDEX(Data,SMALL(IF((QTY>=IFERROR(--LEFT($A$3,4),250))*(QTY < IFERROR(--RIGHT($A$3,4),99^99))*
(COUNTIF($A$1,SIZE)+($A$1=""))*(COUNTIF($A$2,SURFACE)+($A$2="")),ROW(Data)-ROW(INDEX(Data,1,1))+1),ROWS(A$5:A5)),COLUMNS($A5:A5)),"")

Markmzz
 
Last edited:
Upvote 0
markmzz said:
Try this:

Code: 
Create this names:

Data    - Refers To: =Sheet1!$A$2:INDEX(Sheet1!$L:$L,MATCH("ZZZZZ",Sheet1!$A:$A),0)

QTY    - Refers To: =Sheet1!$J$2:INDEX(Sheet1!$J:$J,MATCH("ZZZZZ",Sheet1!$A:$A),0)

SIZE    - Refers To: ==Sheet1!$D$2:INDEX(Sheet1!$D:$D,MATCH("ZZZZZ",Sheet1!$A:$A),0)

SURFACE    - Refers To: =Sheet1!$E$2:INDEX(Sheet1!$E:$E,MATCH("ZZZZZ",Sheet1!$A:$A),0)


Formula

[COLOR=#ff0000][B]In A5 [/B][/COLOR]- - use Ctrl+Shift+Enter and not only Enter to enter the formula

For Excel 2003

=IF(SUM((QTY>=IF(ISNUMBER(-LEFT($A$3,4)),--LEFT($A$3,4),250))*(QTY < IF(ISNUMBER(-RIGHT($A$3,4)),--RIGHT($A$3,4),99^99))*
(COUNTIF($A$1,SIZE)+($A$1=""))*(COUNTIF($A$2,SURFACE)+($A$2=""))) < ROWS(A$5:A5),"",
INDEX(Data,SMALL(IF((QTY>=IF(ISNUMBER(-LEFT($A$3,4)),--LEFT($A$3,4),250))*(QTY < IF(ISNUMBER(-RIGHT($A$3,4)),--RIGHT($A$3,4),99^99))*
(COUNTIF($A$1,SIZE)+($A$1=""))*(COUNTIF($A$2,SURFACE)+($A$2="")),ROW(Data)-ROW(INDEX(Data,1,1))+1),ROWS(A$5:A5)),COLUMNS($A5:A5)))

For Excel 2007+

=IFERROR(INDEX(Data,SMALL(IF((QTY>=IFERROR(--LEFT($A$3,4),250))*(QTY < IFERROR(--RIGHT($A$3,4),99^99))*
(COUNTIF($A$1,SIZE)+($A$1=""))*(COUNTIF($A$2,SURFACE)+($A$2="")),ROW(Data)-ROW(INDEX(Data,1,1))+1),ROWS(A$5:A5)),COLUMNS($A5:A5)),"")

Markmzz
Click to expand...

The cell took the formula and the result is blank cell , I have requested to send the file to your mail , I wish I can take it too work 2moro
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Question
Conditional formating where there are 2 values in a cell
Replies
3
Views
927
Godders199
G
S
  • Question
Conditional Formating Required for changing Dates based on 2 different cell dates
Replies
1
Views
694
Toadstool
Toadstool
N
  • Question
excel macro to split data into multiple worksheets based on conditions worksheet
Replies
4
Views
4K
Fluff
Fluff
B
  • Question
Matching Number To Number In text
2 3
Replies
23
Views
2K
Twollaston
T
F
  • Question
Excel data matrix into a pivot
Replies
5
Views
3K
alansidman
A

Forum statistics

Threads
1,215,352
Messages
6,124,453
Members
449,161
Latest member
NHOJ

Share this page

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