Well, in my example, I'd list out all 100 warehouses and probably use
conditional formatting to quickly highlight who has inventory (e.g., turn
cell green if count 1). You can also use autofilter to list the top 10, 20
or 50) warehouses by count.
--
Tips for Excel, Word, PowerPoint and Other Applications
http://www.kan.org/tips


"kp0250" wrote:

I'm in the process of reading the "tips" page, but is there a way for it to
"spit" out the name of the warehouse of what meets the criteria instead of
the number? Because in your example it says warehouse 1 has 180 (i think that
was the number) but that could be in various warehouses, is there a way for
it to tell me what warehouses they are in ?

Thanks for your time in helping us.

"M Kan" wrote:

VBA is visual basic
--
Tips for Excel, Word, PowerPoint and Other Applications
http://www.kan.org/tips


"kp0250" wrote:

What is a vba?

And i will look into that. Hopefully it will help me.

THanks

"M Kan" wrote:

Not sure that you could do this in a single formula, without VBA, but you can
approach like this:

Step 1
In the top couple of rows of a summary sheet, identify your inputs: type
(b2), size (b3), thickness (b4). I'll assume for now that these are the only
dimensions you need to search on.

Below your input cells, create a row for each warehouse and use the
following formula:

=SUMPRODUCT(--(warehouse1!range_of_type=B2),--(warehouse2!range_of_size=b3),--(warehouse1!range_of_thickness=B4),warehouse1!rang e_of_count))

This should give you the count of items meeting your specs for Warehouse1.
You can use INDIRECT to make the sheet name dynamic, though I don't have a
resource written up to explain this, though a couple of other people have
posted good links.

I thought it would just be easier to post an example of what I mean
http://www.kan.org/tips/files/inventory_example.xls

Here's an article on the sumproduct function referenced above.
http://www.kan.org/tips/excel_sumproduct_advanced1.php

You can further use data validation to control your inputs to make sure they
are specified correctly.

--
Tips for Excel, Word, PowerPoint and Other Applications
http://www.kan.org/tips


"kp0250" wrote:

I'm trying to help my hubby with work. He has about 100 worksheets, sometimes
more. Each worksheet represents a warehouse they have inventory stored in.
How would I searh each worksheet to meet certain criteria? Like for instance
this is how its set up
A b c d
count type size thickness
4 comp 4x4 4

Each warehouse (which is its own worksheet) has an undefined number of
records and its constantly changing as they add more and sell others. He
needs to like search ALL warehouses to say bring up the warehouses that meet
all the criteria, like all that have the Comp type in size 4x4 with a 4in
thickness. This way he doesn't waste the day literally searching all
worksheets manually. It needs to "meet" all the criteria that he has
specified. Like if its a comp type but size 2x8 with a 4 in thickness, then
he doesn't want to know about it... Basically when they sell it, with what
ever specifications the customer want, they need to be able to punch it in
and pull up a list of warehouses that have that exact material stored in it.
I have been trying to get this done but my knowledge of excel is better than
his but still limited. Can anyone point me in the right direction? I have
tried the Hlookup but can seem to get it on more then 1 sheet, so i think i
might need to use something else, i just don't know what.

Any help will be greatly appreciated.