Working with 2 worksheets

R

Rob

Hello all,
I am working with 2 different worksheets and need to have info from 1 auto
over to the other in certain cells. 7 sales people on sheet 1 have multiple
sales with multiple forms of payments (ie, CC, Cash Wire etc.). Sheet 2 is a
total for the day, week, month etc. and list each sales person in A1:A7 with
the forms of payment in columns B through H. I know there is a way to match
the forms of payment with the sales person on Sheet 1 to auto populate in the
corresponding cells on sheet 2 but I am drawing a blank. Any help would be
appreciated. Thanks to all...
 
B

Bassman62

With the layout you discribe for Sheet 2, I'm not sure how you'd report
totaling by day, week, month.
You may want to consider using Pivot tables.

As for returning the data as you described here;
the forms of payment in columns B through H. .... a way to match
the forms of payment with the sales person on Sheet 1

With Sheet1: Headers on Row 1.
Col A: Sales Associate, Col B: Date, Col C: Payment Type, Col D: Amount
Sheet2: Headers on Row 1.
Col A: Sales Associate, Col B: PaymentType1, Col C: Type2, ...Col H: Type7

In Sheet2, B2 enter this formula.
=SUMPRODUCT(--(Sheet1!$A$2:$A$100=$A2),
--(Sheet1!$C$2:$C$100=B$1),Sheet1!$D$2:$D$100)

This formula can be copied down and to the right as needed.
Assumes data on Sheet1 stops at row 100. Modify to fit your data range.
You may want to use validation on Sheet1 to assure that the Associates and
Payment types are spelled the same on both sheets.
Hope this helps.

Regards,
Dave
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top