Excel search and show 2

[apex1x]

I'll try to explain this as best as possible.

I have several worksheets, one of which is a sheet for just the totals of the other sheets.

On the worksheets, there are various products listed vertically, and beside those are the quantity and price.

Basically, what I want to do is have the names of whatever products which have more than 5 for quantity, and then have those product names show up in some sort of list underneath the data in the totals worksheet.

Can I do this with simple If statements or do I need advanced macros/VBA??
Any ideas/suggestions would be welcome.

Let me know if you are unclear on what I'm trying to do. If you can understand it, you probably don't.

 

[Zathras]

You can do it with extra hidden columns in each sheet and some rather complex formulas, as long as you don't mind the recap from the other sheets being in different columns:

e.g. you could disply something like this:

Recap of high-q products:
   Sheet1     Sheet2     Sheet3
   Widgets    Items      Gegaws
   Things     Dodads     Freebies
   Stuff                 Gadgets

However, VBA would definitely be easier in the long run for maintenance purposes. If you want to see the formulas I'm talking about, post again and I will write them up here. (See below for a sample.) Conversely, if you want to see the VBA solution, let me know and I (or someone else here) can work that up for you.


Sample formulas to give you an idea:

A13: =$A$11&"!$D$"&(B12+1)&":$D$6000"
B13: =MATCH(TRUE,INDIRECT(A13),0)+B12+1
E13: =IF(ISNA($B14),"",INDEX(Sheet1!$A$2:$C$6000,$B13,1))

 

[bandit600]

It may be that I have not understood properly, but a simple solution if you don't need to see both all the original data and the subset together, would be to filter on the quantity column and switch between the views.

I hope I have not oversimplified the problem.

bandit600

 

[Zathras]

Bandit600 makes a good point. It all depends on exactly how many sheets "several" is.

If you have 10 or so sheets, it wouldn't be too bad of a job to use auto-filter. Even so, you could still benefit from using VBA to switch the filters on and off.

BTW, if you have more than 80 sheets, my formula approach wouldn't work very well since it requires 3 columns (2 hidden) on the summary sheet for each detail sheet.

It would help if you could provide explicit details of how your data is stored on the various sheets you have.

 

[apex1x]

Ok, some great tips so far.
To be more specific, the layout is as follows:

5 worksheets:

1-Sewing
2-Manufactured
3-Purchased
4-Chairs
5-Totals

On the first 4 worksheets, there are lists of the various items, with the quantity inputted beside each item. There are various other fields which are not significant here.

I want all of the items on the first four worksheets which have 5 or more quantity to be listed on the totals sheet somewhere.

I can give ya an exact layout of each sheet but that would take a while... I'm sure if i got general formulas/ideas I can work it out. Or I could send the workbook to someone if its desperate. Thanks.

If you can understand it, you probably don't.

 

[Zathras]

Assuming that each "detail" sheet is set up something like this:

A1: 'ITEM
A2: 'Things
A3: 'Gadgets
A4: 'Dodads
B1: 'QUANTITY
B2: 3
B3: 7
B4: 6
C1: 'PRICE

(etc.)
And assuming that the "Totals" sheet is set up something like this:

A10: 'High-Quantity Items:
A11: 'CATEGORY
B11: 'ITEM
C11: 'QUANTITY

Then set up a macro module with the following:

Option Explicit

' When the workbook arrangement changes, the following
' may need revision:

Const TOTAL_SHEET = "Totals"
Const HIQ_RANGE = "$A$12:$C$6000"
Const ITEMNAME_COLUMN = 1
Const QUANTITY_COLUMN = 2
Const HIGH_QUANTITY = 6

Sub RecapHiQ()
Dim sh As Worksheet
  ThisWorkbook.Sheets(TOTAL_SHEET).Range(HIQ_RANGE).Clear
  For Each sh In ThisWorkbook.Sheets
    If sh.Name <> TOTAL_SHEET Then RecapQ sh
  Next sh
End Sub

Private Sub RecapQ(DetailSheet As Worksheet)
Dim sCategory As String
Dim sItemName As String
Dim nQuantity As Integer
Dim c As Range
Dim nBlankRows As Integer
  sCategory = DetailSheet.Name
  For Each c In DetailSheet.Range(&quot;A2:A65000&quot;)
    nQuantity = c.Cells(1, QUANTITY_COLUMN)
    If nQuantity >= HIGH_QUANTITY Then
      sItemName = c.Cells(1, ITEMNAME_COLUMN)
      PostHighQ sCategory, sItemName, nQuantity
    Else
      If nQuantity = 0 Then
        nBlankRows = nBlankRows + 1
      Else
        nBlankRows = 0
      End If
    End If
    If nBlankRows > 6 Then Exit Sub
  Next c
End Sub

Private Sub PostHighQ(Category As String, Item As String, Quantity As Integer)
Dim TotalSheet As Worksheet
Dim r As Range
  Set TotalSheet = ThisWorkbook.Sheets(TOTAL_SHEET)
  Set r = TotalSheet.Range(HIQ_RANGE).Cells(1, 1)
  If r.Formula <> &quot;&quot; Then
    If r.Offset(1, 0).Formula <> &quot;&quot; Then Set r = r.End(xlDown)
    Set r = r.Offset(1, 0)
  End If
  r.Cells(1, 1) = Category
  r.Cells(1, 2) = Item
  r.Cells(1, 3) = Quantity
  Set r = Nothing
  Set TotalSheet = Nothing
End Sub

Then run the macro "RecapHiQ" (Tools/Macro/Macros... and Run). You can make it fancier by dropping a button on the Totals sheet and assigning the macro to it. Or you can simply use the Macros... Options... button and assign a short-cut key.

 

[apex1x]

Ok, i'm curious how to adjust this to fit the worksheets I am using.

On each detail sheet, the items start to be listed on the 4th row. Above that, is just labelling, etc.

I do not see any references to the actual names of the worksheets, except for "totals" worksheet.

Does this code go through them automatically without having to call them explicity?
If you can understand it, you probably don't.

 

[Zathras]

The code assumes that all of the sheets except the one with the tab "Total" are involved in the process, and that all of the detail sheets have the same basic setup (i.e., data beginning on line 4, item name in column A, quantity in column B). This makes it easy to add sheets for additional details without needing to modify the macro.

To begin processing with the 4th row, change this line:

  For Each c In DetailSheet.Range("A2:A65000")

to read:

  For Each c In DetailSheet.Range("A4:A65000")

If you have other sheets in the workbook besides the detail sheets and the "Total" sheet, post back with the specifics and I can indicate the modifications required.


The lines of code that read:

   For Each sh In ThisWorkbook.Sheets
     If sh.Name <> TOTAL_SHEET Then RecapQ sh
   Next sh

do exactly as you guessed, namely causes each sheet in the workbook to be processed (except for the one called "Totals" as identified by the constant TOTAL_SHEET)

 

[apex1x]

This is what happens when I run this macro.
It goes in an endless loop and i must manually break it.
This stuff comes up on the bottom of the "totals" sheet.
The placement is right but I dont understand where this came from :S



TOTALS MANUFACTURED PARTS 45
TOTALS PURCHASED PARTS 69
TOTALS TOTAL: 124
TOTALS TOTALS 45
TOTALS TOTALS 69
TOTALS TOTALS 124
TOTALS TOTALS 45
TOTALS TOTALS 69
TOTALS TOTALS 124
TOTALS TOTALS 45
TOTALS TOTALS 69
TOTALS TOTALS 124
TOTALS TOTALS 45
TOTALS TOTALS 69
If you can understand it, you probably don't.

 

[apex1x]

Just a reminder that basically all I want is for the items with more than 5 "quantity" to be listed where those messed up numbers are.
The quantity column is "C" or the third column. I've adjusted the Const QUANTITY_COLUMN = 3 .

Any suggestions?
If you can understand it, you probably don't.

 

[Zathras]

The name of your totals sheet is identified (to the macro) by the constant expression:

Const TOTAL_SHEET = "Totals"

It looks like the name of your totals sheet is really "TOTALS" not "Totals" as you indicated on your Feb 14 post.

You can do either one of two things:
(1) Name the totals sheet "TOTALS" (all caps), or
(2) Modify the macro so that the constant reads:

Const TOTAL_SHEET = "TOTALS"

Either way should make it work the way you want, as long as you're consistent.

 

[apex1x]

Ok, now I get a type mismatch error! My favorite.
The quantity is sometimes blank and sometimes an integer. Does it have to have a zero there or are blanks causing this error?
If you can understand it, you probably don't.

 

[Zathras]

An empty cell would not cause the error, but if you type anything that is not a number (like a space) it would cause problems.

The fix is to replace the line (in RecapQ subroutine)

  nQuantity = c.Cells(1, QUANTITY_COLUMN)

with

  If IsNumeric(c.Cells(1, QUANTITY_COLUMN)) Then
    nQuantity = c.Cells(1, QUANTITY_COLUMN)
  Else
    nQuantity = 0

I didn't anticipate that the quantity cell might have text in it. But I suppose someone might type "3 on order" or the like.

Then again there are those who "blank" a cell by hitting the space bar instead of pressing the delete key.

 

[Zathras]

Sorry, left off the "End If"

The replacement should be:

  If IsNumeric(c.Cells(1, QUANTITY_COLUMN)) Then
    nQuantity = c.Cells(1, QUANTITY_COLUMN)
  Else
    nQuantity = 0
  End If

 

[apex1x]

Ya, I gathered that
Hmm... still the same error...
Must be something else causing this. If you can understand it, you probably don't.

 

[Zathras]

What line does it stop at when you get the error?

 

[apex1x]

After the error, no lines are highlighted... Is there an option I set for this? If you can understand it, you probably don't.

 

[Zathras]

When you get the error message, click the button marked "Debug" -- You should then find yourself in the VBA editor window with the offending line highlighted in yellow. You can use the mouse to hover over various parts to see current values. That may also give you a clue.

Please copy and paste the highlighted line into a post so I can get some inkling of what is happening. Remember, I'm flying blind here.

 

[apex1x]

I do not have a "debug" option as you are saying.
To write the VBA code, I am in the VB editor window already. There is a Debug menu but nothing seems to help out in this situation.
When the error comes up, no lines are highlighted or anything. I'm at a loss here... If you can understand it, you probably don't.

 

[Zathras]

"Ok, now I get a type mismatch error! My favorite."

Please describe exactly what you do to cause this phrase to appear. The only time I remember seeing it is when it is displayed in a dialog box that looks something like this:

.-----------------------------------------------.
| Microsoft Visual Basic                        |
|-----------------------------------------------|
|                                               | 
|    Run-time error '13'                        |
|                                               |
|    Type mismatch                              |
|                                               | 
|                                               | 
|                                               | 
|  [Continue]  [  End  ]  [ Debug ]  [ Help ]   |
'-----------------------------------------------'

How and where are you seeing "Type mismatch"?

Perhaps it would help if you copy and paste the macro code back into a post here so I can see the changes you have made.