Find, sum and return value from 2nd spreadsheet

[Jade]

Hi,

I'm an excel newbie so hopefully someone will be able to help! I have a 2 spreadsheets where:

Spreadsheet "Employee Info" has in each row:
Column A lists: Employee Names
Column D lists the employee's: Store Name
Column I will eventually list the employee's: Sales YTD 2006

The second spreadsheet is called "data" and lists in each row

Column A: employee's name
Column D: the employee's store name
Column F: date of sale
Column G: dollar value of the sale.

The problem is that in "data" each employee can be listed multiple times as they may have multiple sales and that sales values from year 2005 are also included.

I'm trying to build a macro that:

1. Takes the employee's name and store name from "Employee Info"
2. Finds all rows in "data" that matches the same employee's name and store name
3. Sums the employee's sales but ONLY for sales completed in year 2006
4. Takes that sum and inputs it into column C in "Employee Info"

I've tried posting this elsewhere but didn't get very far; the vb code in the attached spreadsheet doesn't work....can anyone help?

Much appreciated!

[duane]

you can do that without a macro, just putting a sumproduct formula in your employee info sheet to gather the data from the data sheet, This assumes you know the emplyee names and can input them into the employee info sheet.

Of course, if you want to create the list of employees automatically as new employees are added, a macro will do just fine.

Which way do you want to go?

[Garf]

Is that 2 seperate workbooks (spreadsheets) or 2 worksheets within the same workbook (Spreadsheet)

[Bryan Hessey]

Hi,

I'm an excel newbie so hopefully someone will be able to help! I have a 2 spreadsheets where:

Spreadsheet "Employee Info" has in each row:
Column A lists: Employee Names
Column D lists the employee's: Store Name
Column I will eventually list the employee's: Sales YTD 2006

The second spreadsheet is called "data" and lists in each row

Column A: employee's name
Column D: the employee's store name
Column F: date of sale
Column G: dollar value of the sale.

The problem is that in "data" each employee can be listed multiple times as they may have multiple sales and that sales values from year 2005 are also included.

I'm trying to build a macro that:

1. Takes the employee's name and store name from "Employee Info"
2. Finds all rows in "data" that matches the same employee's name and store name
3. Sums the employee's sales but ONLY for sales completed in year 2006
4. Takes that sum and inputs it into column C in "Employee Info"

I've tried posting this elsewhere but didn't get very far; the vb code in the attached spreadsheet doesn't work....can anyone help?

Much appreciated!

Hi,


=SUMPRODUCT(--(data!A$2:A$31=A6)*(--(data!D$2:D$31=D6)*(--(LEFT(data!F$2:F$31,4)="2006")*(data!G$2:G$31))))

but with CSE (CTRL/Shift/Enter) will extract the figures, as per the attached

note, I have not checked your macro, but one name, Saunders,Debbi, was missing from the Employee Info sheet

hth
---