How do I compare cells in multiple sheets in a workbook

I have a worksheet with 18 separate tabs. Each tab contains a different hotel list for a different NFL team for the 2007 season. Some of these hotels were hosting more then one team and so appear on multiple lists. How can I find out which hotels are duplicated. For example if a hotel is under the "Detroit Lions" tab and the "Chicago Bears" tab, then I would want it to tell me that it was in the "Bears" tab when I was looking at the "Lions" tab and vice versa. Basically I would want a cell dedicated to telling me what other tabs that hotel name is also located. The cell would tell me every other tab that hotel name appears except for the one I am looking at. Can anyone help me?

Hi,

It's not particularly easy with your 18 sheet layout. The simplest option would be to create an index list on a 19th sheet, and extract the sheet name and all the hotels on that sheet. You'd end up with a long two column list, which you could then analyse with standard functions fairly easily.

To speed up the creation of this list you could write a simple macro to loop though all the sheets, copying and pasting from each one.

I'd also be inclined to review your workbook layout. It sounds like you've got a typical database here, which I suggest might be better handled on a single sheet.

HTH