Multiple worksheet search

[smannon]

I have 10 worksheets with sales from customers. I need to consolidate the sales by month on a single worksheet. The challange is finding out whether or not the worksheets contain the same data as the worksheets are all invoice sales.

Here is an example:

Worksheets Jan-Oct
They contain the following fields:
Acct# ; KSAcct#; CustomerName; Address, City, State, Zip, Phone, NetSales

I need to find customer "X" on each sheet (Jan-Oct) and then total the Net Sales.

The hard part is that this is sales by invoice not sales by customer so the customer may or may not be on each sheet!

I combined all customers onto one sheet from each month which now contains 67710 rows of data. Yes this is probably over kill but I can't seem to find any other way of insuring I account for each customers sales or to find out if there are even duplicates within the data I am trying to sum.

I tried using the sumif but that doesn't seem to be what I need...Thanks for you assistance!

 

[BenMiller]

Once you find Customer X, is the data below the cell with the name? Or to the right? And is there more than one cell which you need to Sum? And are they always in the same position relative to the customer's name?

 

[Aladin Akyurek]

I have 10 worksheets with sales from customers. I need to consolidate the sales by month on a single worksheet. The challange is finding out whether or not the worksheets contain the same data as the worksheets are all invoice sales.

Here is an example:

Worksheets Jan-Oct
They contain the following fields:
Acct# ; KSAcct#; CustomerName; Address, City, State, Zip, Phone, NetSales

I need to find customer "X" on each sheet (Jan-Oct) and then total the Net Sales.

The hard part is that this is sales by invoice not sales by customer so the customer may or may not be on each sheet!

I combined all customers onto one sheet from each month which now contains 67710 rows of data. Yes this is probably over kill but I can't seem to find any other way of insuring I account for each customers sales or to find out if there are even duplicates within the data I am trying to sum.

I tried using the sumif but that doesn't seem to be what I need...Thanks for you assistance!

Create a range housing the names of the relevant sheets. Name this range as SheetList via the Name Box on the Formula Bar.

Now invoke:

=SUMPRODUCT(SUMIF(INDIRECT("'"&SheetList&"!C2:C400"),"X",INDIRECT("'"&SheetList&"!I2:I400")))

where C2:C400 on each is supposed to house customer names and I2:I400 net sales. Adjust the ranges and "X" to suit.

 

[smannon]

I have found another way to accomplish what I needed...Pivot Tables made a huge difference. Challange then was to compact it to a smaller size since there were over 100K records.

I just copied and pasted into another workbook and fixed that size issue.

thanks for the reply though!

 

[Aladin Akyurek]

I have found another way to accomplish what I needed...Pivot Tables made a huge difference. Challange then was to compact it to a smaller size since there were over 100K records.

I just copied and pasted into another workbook and fixed that size issue.

thanks for the reply though!

Good idea. PT can be efficient here.