How to pull unique data from another sheet

May there be multiple B/L hyperlinks active on any day... or just the one?

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

Yep. Ordinary formula in O4, copied down:

=IFERROR(INDEX(INDIRECT("'"&$C4&"'!V11:V20"),MATCH(B4,INDIRECT("'"&$C4&"'!U11:U20"),0)),"")

Do check out the format of your summary sheet and let me know if there are issues. Other than that...

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

Mission creep. I'll have to ruminate on this. Can I radically rearrange the summary shet, if needed?

Been away a lot. Nearly done though. I'm probably going to create an extra sheet and put almost all the cr@p int it. A couple of Qs, though.

What is the MAXIMUM number of B/L's that you will have to take into account at any time?

What is the MAXIMUM number of ACTIVE (ie containing stock) stock lines in a B/L that you are likely to have? I suspect that this number will be truly horrendous!!

I suspect that you will need VBA to do this. Picking up sheet names using INDIRECT is not hugely efficient. With that level if activity your PC will probably melt....

I'll post something a bit later and you can have a look.

Ths is as far as a formula-based solution can go... I think. Take a look. It does all that you want; but it'll probably not work on such a lrge scale.


Enable macros on opening to enable a sheet list to be generated in column A of Help.

Happy to explain...