Return all data based on Cell value

[saylesh]

Hi there,
I have this formula below which works perfectly filtering data I need.

{=IFERROR(INDEX(STOCKLIST!$A$8:$P$3881,SMALL(IF(($B$1=STOCKLIST!$B$8:$P$3881),ROW(STOCKLIST!$B$8:$P$3881)-MIN(ROW(STOCKLIST!$B$8:$P$3881))+1,""),ROW(A1)),0),"")

I was wondering if there was a way to have it so the if B1 is blank or I enter * it returns all data from the STOCKLIST sheet.

Thanks in advance.

[TMS]

Untested, but try,

Please Login or Register  to view this content.

Array enter with Ctrl-Shift-Enter rather than just Enter

[saylesh]

Thanks for your reply.

I tried this but its returning the first line of the stocklist sheet repeatidly.

=IF($B$1="",STOCKLIST!$A$8:$P$3881,(IFERROR(INDEX(STOCKLIST!$A$8:$P$3881,SMALL(IF(($B$1=STOCKLIST!$B$8:$P$3881),ROW(STOCKLIST!$B$8:$P$3881)-MIN(ROW(STOCKLIST!$B$8:$P$3881))+1,""),ROW(A1)),0),"")))

[TMS]

Fast answers need visual help. Please read the yellow banner at the top of this page on how to attach a file and a mocked up solution.

[saylesh]

here's a sample sheet.