i have about 100 worksheets, how do i "lookup" multiple criteria

[kp0250]

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.

 

[Tim879]

Are there too many rows to just combine each warehouse into a single
excel sheet? The fact that you have each warehouse on it's own sheet
is making this task a lot harder than it should be.

If you can combine all of the warehouses onto 1 sheet, then you can
use autofilter on the consolidated sheet to search and filter for the
various criteria you need or to get a single warehouse view.

If you search this newsgroup there are various macros to consolidate
all of the worksheets into 1 tab or you can just use the one attached
below.

One other suggestion, if you do follow this tip, before consolidating
the spreadsheets, all the warehouse name to the data in each table.
(i.e.
if you data is set up like this
Type Part etc
change it so it looks like this
warehouse type part etc.

to do this:
Select all 100 tabs (click on the first tab, scroll to the last tab,
hold shift and click on the last tab - all will be selected)

Insert a column before column A.
Name the column warehouse in Cell A1
In cell A2 enter =RIGHT(CELL("filename",A2),LEN(CELL("filename",A2))-
FIND("]",CELL("filename",A2)))

Copy this formula down.
Check to make sure that the formula copied down to the last row on
each tab.

Finally, make sure to copy / paste special values to change the
warehouse name from a formula to text so that it doesn't change when
you consolidate all of the files.

Here's the macro to consolidate all of the data.

Sub Combine_Sheets()
Dim Wsht As Worksheet
Dim i As Integer
Dim NewSheetName As String


If MsgBox("This macro will create a new tab in the workbook called
Summary and then " & _
"copy / paste every other tab in the workbook into
this tab." & Chr(10) & _
"Do you want to continue?", vbYesNo) <> vbYes Then
Exit Sub

Application.ScreenUpdating = False
'Add a worksheet for the summary as the 1st sheet in the book

Sheets(1).Select
Set Wsht = ActiveWorkbook.Worksheets.Add


NewSheetName = " Summary "
' While Validate_New_Sheet_Name(NewSheetName) = False
' NewSheetName = InputBox("Sheet already exists in workbook.
Please enter a new name for tab", "Error: Sheet already exists",
"Summary")
' Wend

Wsht.Name = NewSheetName

'skip the summary sheet so start with sheet 2
For i = 2 To ActiveWorkbook.Sheets.Count
'select the next sheet
Sheets(i).Select

'copy the data
Range("A1",
ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Copy ' Copy selection

'go to last used row of the summary sheet
Wsht.Select
Range("A" & ActiveCell.SpecialCells(xlLastCell).row +
1).Select

'paste the data
Selection.PasteSpecial
Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False ' paste values
Application.CutCopyMode = False ' exit copy / paste
Mode
Range("A1").Select
Next

Application.ScreenUpdating = True
End Sub

 

[M Kan]

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!range_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.

 

[kp0250]

Combining it isn't an option. Each department needs different data and its
all on a shared drive, which is why its set up the way it is, so that the
different dept. can access and do what they need done.

He read your reply and flat out told me that combining into 1 worksheet
wasn't an option. Thanks for your time and your reply though.

Are there too many rows to just combine each warehouse into a single
excel sheet? The fact that you have each warehouse on it's own sheet
is making this task a lot harder than it should be.

If you can combine all of the warehouses onto 1 sheet, then you can
use autofilter on the consolidated sheet to search and filter for the
various criteria you need or to get a single warehouse view.

If you search this newsgroup there are various macros to consolidate
all of the worksheets into 1 tab or you can just use the one attached
below.

One other suggestion, if you do follow this tip, before consolidating
the spreadsheets, all the warehouse name to the data in each table.
(i.e.
if you data is set up like this
Type Part etc
change it so it looks like this
warehouse type part etc.

to do this:
Select all 100 tabs (click on the first tab, scroll to the last tab,
hold shift and click on the last tab - all will be selected)

Insert a column before column A.
Name the column warehouse in Cell A1
In cell A2 enter =RIGHT(CELL("filename",A2),LEN(CELL("filename",A2))-
FIND("]",CELL("filename",A2)))

Copy this formula down.
Check to make sure that the formula copied down to the last row on
each tab.

Finally, make sure to copy / paste special values to change the
warehouse name from a formula to text so that it doesn't change when
you consolidate all of the files.

Here's the macro to consolidate all of the data.

Sub Combine_Sheets()
Dim Wsht As Worksheet
Dim i As Integer
Dim NewSheetName As String


If MsgBox("This macro will create a new tab in the workbook called
Summary and then " & _
"copy / paste every other tab in the workbook into
this tab." & Chr(10) & _
"Do you want to continue?", vbYesNo) <> vbYes Then
Exit Sub

Application.ScreenUpdating = False
'Add a worksheet for the summary as the 1st sheet in the book

Sheets(1).Select
Set Wsht = ActiveWorkbook.Worksheets.Add


NewSheetName = " Summary "
' While Validate_New_Sheet_Name(NewSheetName) = False
' NewSheetName = InputBox("Sheet already exists in workbook.
Please enter a new name for tab", "Error: Sheet already exists",
"Summary")
' Wend

Wsht.Name = NewSheetName

'skip the summary sheet so start with sheet 2
For i = 2 To ActiveWorkbook.Sheets.Count
'select the next sheet
Sheets(i).Select

'copy the data
Range("A1",
ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Copy ' Copy selection

'go to last used row of the summary sheet
Wsht.Select
Range("A" & ActiveCell.SpecialCells(xlLastCell).row +
1).Select

'paste the data
Selection.PasteSpecial
Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False ' paste values
Application.CutCopyMode = False ' exit copy / paste
Mode
Range("A1").Select
Next

Application.ScreenUpdating = True
End Sub

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.

 

[kp0250]

What is a vba?

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

THanks

 

[M Kan]

VBA is visual basic

 

[kp0250]

I remember seeing something / somewhere about and add-in with VBA, would that
help me?

I am looking at the example right now.

Thanks again

 

[kp0250]

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]

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.