compiling data from one book to another...too many "ifs"

[jacob]

I made a spreadsheet a couple of years ago and it took forever. I need
something similar and I'm determined to find an easier way!

I have rows of data in Book1. I would like to pull certain columns of
data from these rows and add them up in a neat TOTALS grid in Book2,
with a condition.

For instance, this is my general set up:
Book1
ColumnA: Name (salesperson)
ColumnB: number (units)
ColumnC: number (volume)
ColumnD: (either blank or an "x")

Each row has different names and associated data in the appropriate
columns. I'd like to pull each salesperson's units and volume into
Book2, depending on whether there is an X in Column D (No X = don't
pull over any data from that row; X = pull data over)

So I can write a million IF statements or there is an easier way I'm
hoping someone can point out for me. (And perhaps instead of text "x's"
i could use a button or pivot table or something??)

I hope I've explained what I'm trying to do well enough...if not please
let me know. Thank-you, in advance, for any help you can provide!

Jacob

 

[Guest]

Test this macro on copies of the source and destination workbooks first. The
macro is designed to be run from the destination wb and to select and open
the source wb using a dialog called by the macro. Minimal testing.

Where:
1. wb1 = destination workbook.
2. wb2 = source workbook.
3. wks1 = destination worksheet.
4. wks2 = source worksheet.
5. Destination worksheet name = "Current Sales Data".
6. Source worksheet name = "Sales Data".
7. First destination cell is cell A2 (A1 assumed to be a header).
8. First source cell is cell A2 also.
9. There is assumed to be no gaps in the source "Sales Person" data else the
macro will stop at the gap.

Sub TransferSalesData()
Dim wb1 As Workbook, wb2 As Workbook
Dim wks1 As Worksheet, wks2 As Worksheet
Dim r As Range, c As Range, cc As Range
Dim FNm As Variant

Set wb1 = ActiveWorkbook
Set wks1 = wb1.Sheets("Current Sales Data")
Set c = wks1.Range("A2")
FNm = Application.GetOpenFilename("Excel files(*.xls), *.xls", _
Title:="Transfer Sales Data")
If VarType(FNm) = vbBoolean Then Exit Sub
Application.ScreenUpdating = False
Set wb2 = Workbooks.Open(FNm)
Set wks2 = wb2.Sheets("Sales Data")
wb1.Activate
Set r = wks2.Range(wks2.Range("A2"), wks2.Range("A2").End(xlDown))
For Each cc In r.Cells
If Trim(UCase(cc(1, 4))) = "X" Then
c.Resize(1, 3).Value = cc.Resize(1, 3).Value
Set c = c(2)
End If
Next
Application.ScreenUpdating = True
End Sub

Regards,
Greg

 

[Ardus Petus]

Did you try Data>Filter>automatic Filter or Elaborate Filter.

This should help

 

[Tom Ogilvy]

Creating a pivot table would do it all in one step and should be exactly
what you want.

See Debra Dalgleish's pages on Pivot Table if you are not familiar with them

http://www.contextures.com/tiptech.html

see pivot tables in the index.

 

[Doug Glancy]

jacob,

It sounds like a good situation for a pivot table.

hth,

Doug

 

[jacob]

All:

I have no idea how to create a pivot table. I would need a working
example or somethign to show me how. I'm good with forumulas,
formatting, etc., but have just never done a pivot table before.

Would anyone be able to show me how? And I could send you the excel
spreadsheet for you to take a look at what i'm trying to do. The
website above only helps specific pivot table questions, not creating
one from scratch.

Stuck,

Jacob

 

[Doug Glancy]

Jacob,

I'm not that great with them myself, but I do find the wizard gets me a good
start.

hth,

Doug