Looking for a value in multiple worksheets.

[NT1 via OfficeKB.com]

hi guys,

I have around 70+ worksheets in my workbook. I use my clients' names to name
the worksheets. And each worksheet contains informations on the
corresponding products I am selling to them. For example:

in Column A contains Product number, Column B contains Product Name, and
Column C contains the Product Price.

I wish to search for a Product Name in each of the worksheet (if I am selling
to that client)and display the Price I am selling to that specific client. I
wish to display all the informations on a new worksheet. For example:

In Column A contains company names, Column B contains Product Name. C
contains Product Price.

Is it possible to do so?

Any help is appreciated. Thanks in advance.

Jones

 

[Bernie Deitrick]

Jones,

What you are looking for is database functionality - so you should really be using a database. You
can run a simple macro to combine all the data into a single sheet - describe your sheet layout (for
example: the data on each sheet starts in cell A2 with a header row, and that the data is contiguous
in the first three columns). Then, when you have a database (which will have the client name in the
first column) you can use data filters and pivot tables to look at you data in the way that you
describe.

HTH,
Bernie
MS Excel MVP