Excel - need to pull certain data from multiple worksheets 1

[alisapooh]

I have a workbook with several worksheets in it. Each worksheet has the same format, just different accounts on each worksheet.

For example, say an employee was authorized to work 40 hours on job A (WORKSHEET: A) and worked 45; and authorized 32 hours on job B (WORKSHEET: B) and worked 30. There is a column on each worksheet to show the variance.

I am trying to create a summary sheet of all of the employees with negative variance hours. I don't need all of the lines (Job B +2), just the negative ones (Job A -5).

Is there some easy way to accomplish this without manually looking through each sheet?

Thanks

 

[kjv1611]

If you can use Access, the easiest method, I think, could be to use queries. You could:
1. Build a new table.
2. Have an append query to append the records you desire from each sheet to the new table.
3. Export the table out to Excel.

Another method would be to use VBA.

Private Sub Summarize()
  Dim wb as Workbook
  Dim wsT as Worksheet [green]'Summary Sheet[/green]
  Dim wsF as Worksheet [green]'Every other sheet[/green]
  Dim lngRowF as Long [green]'Row Count of origin data[/green]
  Dim lngRowT as Long [green]'Row Count of destination data[/green]

  Set wb = ActiveWorkbook
  Set wsT = wb.Worksheets("Summary")
  lngRowT = 2

  For Each wsF in wb.Worksheets
    If InStr(wsF.Name,"Summary") Then
      [green]'This fixes it to where you're not summarizing your summary data again![/green]
    Else
      lngRowF = 2 [green]'assuming a header row for titles[/green]
      [green]'Assuming Col A is name, Col D (Col # 4) is variance[/green]
      For lngRowF = 2 To wsF.Range("A65000").End(xlUp)
        If wsF.Cells(lngRowF,4) < 0 Then
          [green]'Don't do anything if the value isn't less than 0[/green]
        Else
          [green]'If Value is less than zero, then add to summary[/green]
          [green]'Assuming Column 1 = Name and Column 2 = Variance in "to" or destination worksheet.[/green]
          wsT.Cells(lngRowT,1).Formula = wsF.Cells(lngRowF,1).Formula
          wsT.Cells(lngRowT,2).Formula = wsF.Cells(lngRowF,4).Formula
          lngRowT = lngRowT + 1 [green]'Get ready for next record[/green]
        End If
      Next lngRowF
    End If
  Next wsF

  lngRowF = 0
  lngRowT = 0
  Set wsF = Nothing
  Set wsT = Nothing
  Set wb = Nothing

End Sub

Now, of course, you'll want to add some Error handling in there once you know it's working correctly. Also, I didn't fully test the code, so there could be an error or two in there, but it should be pretty close if not 100% correct.


--

"If to err is human, then I must be some kind of human!" -Me

 

[kjv1611]

Oh, another option - all in Excel - would be, I believe, to use MS Query. I've not used it extensively, so I can't tell you all the details there without going through the process first on my end.

--

"If to err is human, then I must be some kind of human!" -Me

 

[xlhelp]

An alternate solution: Link->Use Pivot Table for Multiple Ranges

A man has only two choices: He can be right or he can be happy.

 

[xlhelp]

BTW, alisapooh; thanks for asking the question, I learnt something new.

Of the close to 400 billion stars in the Milky Way Galaxy, one for you.

A man has only two choices: He can be right or he can be happy.

 

[alisapooh]

I think what I need is a macro - and I know NOTHING about how to create one other than following the instructions that say create macro.. Start ... stop... etc.

I've attached (I hope it works - it is in a google doc) a sample workbook with the different pages and what I am looking for.

It just needs something like if worksheet JobA cell J8<0, then copy it here; if J8>0 check K8, etc. So it will check about 7 cells on about 15 worksheets. I don't know how to do the keep checking thing so I don't have a bunch of blank entries.

I have created a pivot table that summarizes ALL of the data into a table (rows - job #, columns - category). I can pull from that too... just still don't have a list of what categories are negative.

 

[Gavona]

I don't know how to do the keep checking thing so I don't have a bunch of blank entries.

Not really following the thread and can't seem to get to your workbook (my bad) but why not initially get all the info into your summary sheet then do the testing to delete the unwanted rows ("blank entries"). Maybe a simple filter could do this?


Could use the pivottable approach to bring in the data, copy to values, fill the blanks as necessary so there is a value in every row for every column then filter to remove unwanted rows and/or to identify the negative jobs and add a flag in a further column to those jobs... or use VBA

(VBA queries should be in forum707 )

Gavin