Returning worksheet names

  • Thread starter Thread starter Stu
  • Start date Start date
S

Stu

Is it possible to have a function which would search several (named)
worksheets and return the name of the worksheet(s) which held a value.
I've a worksheet with a complete list of stock items and then five
other worksheets listing items in stock at depots. I want to be able
to put a column next to the stock item on the master sheet showing
which of the depots currently have stock of the item. So in column A
wou woulf have the item number (which is always unique) and then in
Column B I want to show "Depot 1, Depot 4".
 
Stu,

There is no simple way to do this with the setup that you are using, but
fortunately, this is a perfect opportunity for you to learn how to use a
database - even within Excel.

On each of your five sheets, insert another column (in the same place on
each sheet), and in that column enter the Depot Name in any row where you
have an item entered. Then combine all the sheets together into one sheet,
into one big table, and when you want to find which depot has an item, use
the autofilter from the item column to select the item to show. Any depot
that has that item listed will be shown, along with the count of the item
(which you presumably have in your table).

HTH,
Bernie
MS Excel MVP
 
Back
Top