Find value in workbook and return tab name

[dollarbill13]

I have a workbook with several tabs. The last of them "Totals" contains a list of all the values in the first row of each other tab ("Tab 1-10"). I would like to have the last tab find each value in the work book and then return the name of the tab that the item is on...

For example

Tab 1 contains:

aaa
bbb
ccc

Tab 2:
ddd
eee
fff

Tab 3:

ggg
hhh
iii

I would like "Totals" to look like this:

aaa --- Tab 1
bbb --- Tab 1
ccc --- Tab 1
ddd --- Tab 2
eee --- Tab 2
fff --- Tab 2
ggg --- Tab 3
hhh --- Tab 3
iii --- Tab 3

Is this possible?

 

[Domenic]

Assumptions:

A2:A100 on each sheet contains the data

A2 downwards on the summary sheet contains aaa, bbb, ccc, etc.

D2:D11 contains the sheet names

Formula:

On the summary sheet, B2, copied down:

=INDEX($D$2:$D$11,MATCH(TRUE,COUNTIF(INDIRECT("'"&$D$2:$D$11&"'!A2:A100"),A2)>0,0))

...confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!