Glenn wrote:
LiAD wrote:
Hi,

I am looking for a formula that can act as a filter.

In sheet AA I have a table with headings products, date, diameter etc
starting from col A.

I would like in sheet BB to be able just to see data corresponding to
a certain product, call it Cars. The user enters Cars in B2 sheet BB
and the formula fills in the table in sheet BB with only the data for
Cars from sheet AA.

Is this possible without code?

Thanks


Assuming that you have headings on sheet BB from B3 across to match the
headings on sheet AA, put the following array formula (commit with
CTRL+SHIFT+ENTER) in A4 on sheet BB:

=SMALL(IF(ISNUMBER(SEARCH($B$2,AA!$A$2:$A$1000)),
ROW(AA!$A$2:$A$1000),""),ROW(1:1))

Now put this "normal" formula in B4 and copy down and across as needed:

=IF(ISNUMBER($A4),INDEX(AA!A$1:A$8,$A4),"")

You can hide column A in sheet BB.


Forgot to mention that you need to fill A4 down as well.