How to pull unique data from another sheet

OK. take a look at this. I created another B/L sheet and attached it to the summary. I need to create a helper column. If it can't be on the summary sheet, it can be on another (hidden) sheet.

Helper, Summary I5, copied down:
=IF(A5="","",COUNTIF(INDIRECT("'"&A5&"'!V11:V20"),">0"))

counts the number of "active" containers on each B/L.

Then on stock take C4, an array formula, copied down:

=IFERROR(INDEX(Summary!$A$5:$A$20,MATCH(0,--(COUNTIF($C$3:C3,Summary!$A$5:$A$20)=Summary!$I$5:$I$20),0))&"","")

This returns the B/L number the appropriate numberof times, taken from the helper column.

Then in Stock Take A4, copied across and down, a bit of a monster (also an array formula):

=IFERROR(INDEX(INDIRECT("'"&$C4&"'!"&LEFT(ADDRESS(1,COLUMNS($A:A),4),LEN(ADDRESS(1,COLUMNS($A:A),4))-1)&"2:"&LEFT(ADDRESS(1,COLUMNS($A:A),4),LEN(ADDRESS(1,COLUMNS($A:A),4))-1)&"100"),SMALL(IF(INDIRECT("'"&$C4&"'!C2:C100")=$C4,ROW($A$2:$A$100)),COUNTIF($C$4:$C4,$C4))),"")

It looks worsethanit actually is, because there are some bits in there that increment the column letters inside INDIRECT as you copy across. Otherwise it wouldhavelooked like this:

=IFERROR(INDEX(INDIRECT("'"&$C4&"'!A1:A100"),SMALL(IF(INDIRECT("'"&$C4&"'!C2:C100")=$C4,ROW($A$2:$A$100)),COUNTIF($C$4:$C4,$C4))),"")

but you would have to manually change the letters in red in each column. I suspect that htiswill need a bit of tweaking, depending on EXACTLY what your summary sheet lookslike when there are multiple B/Ls present. But it will give you an indication that what you want IS do-able.

Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...