combining multiple sheets

[Guest]

How can I create a consolidated a list of data from multiple worksheets, that
eliminates duplicate entries and gives reference back to the source
worksheet? I tried vlookup but have to make a separate column (and formula)
for each source sheet. That works but it's messy. Can I use an "if"
function that will look for the specified value in multiple worksheets and
then tell me the names of all the sheets where that value is found? The
target value will always be in the same column.

 

[Guest]

Patio --
There are a couple of approaches built in to Excel -- Data>Consolidation,
and Data>PivotTable. I think the documentation is pretty decent. If you use
Consolidation, just remember to check the 'Source Links' box so that your
consolidation page updates along with the source data. The PivotTable has to
be updated manually with a button on the PivotTable toolbar.

HTH