Create a contents list from multiple tabs

Hi there experts!
I have a file with six tabs, and one of the columns in each tab contains reference numbers.

If I have to locate a reference number I need to look in all six tabs, so I want to list all the references on one page so I can filter.
What I want on my 'contents' page ideally is the reference number and the tab name it was found in.
If the cell is blank one of the tabs, I don't want a blank row in my 'contents' page, and I want it to realise when the list has ended in each tab.
So, a lookup union sort of thing. Is this possible? If so, what do you suggest?

Attached example, the 'Reference' column is not always in the same field, I could make it the same field if this was necessary.

I don't understand this: "and one of the columns in each tab is a reference number"

Please read the yellow banner (top) and attach a SMALL sample file that meets the guidelines in that banner.

An UDF solution.

Please Login or Register  to view this content.