sending data between worksheets? 1

[patrichek]

Hi,
I have a spreadsheet with usually around 100 leads a day in it.
I have 20 sales people i distribute the leads to every morning. Is there a way for me to add 20 worksheets to my spreadsheet and distributed the leads evenly among the sales staff? Each new worksheet would represent a sales person.

It would be best if I could distributed them like this:

Lead 1 to Ralph
Lead 2 to Shannon
Lead 3 to Vicky
etc...
Lead 21 to Ralph
Lead 22 to Shannon...

I don't want the first 10 leads going to the first sales person's worksheet the next 10 to the next worksheet and so on.

Thanks!

 

[SkipVought]

Hi,


1) on a separate sheet, make a list of salespersons, and give it a range name Sales_Persons How can I rename a table as it changes size faq68-1331

2) on a separate sheet have your leads, starting in A2, Heading in A1.

3) in B2...
[tt]
=INDEX(Sales_Persons,MOD(ROW()-2,COUNTA(Sales_Persons))+1,1)
[/tt]
and copy down thru your list.

4) Turn on AutoFilter

6) Filter on each Sales Person

OR...

4) do a PivotTable on 20 sheets with the Sales Person in the Page Field.



Skip,

_[red][/red]

 

[patrichek]

Hi,
thanks for your response. I've been trying your solutions but I think I need to be more detailed in my question.

My lead spreadsheet has 4 colums and 100 rows of data, the worksheet with the leads is named "Leads" and the columns are:

Date FirstName LastName State
3/27 John Johnson PA
3/27 Jim Bob TN
3/27 Kathy Kipler MA
3/27 Anthony Sky FL

I want all the data from Leads worksheet row1 to go to salesperson1 on sheet1 (their worksheet), row2 goes to salesperson2 on sheet2, row3 goes to salesperson3 on sheet3, etc.. once i get through my 20 salespeople, who will each have 1 lead now, i want row21 (or lead 21)to go to salesperson1 on worksheet1 row2 and continue parsing the data until all the leads are assigned to a salesperson.

Tomorrow I want to be able to import new leads into this workbook and start the process over again.

Is this possible, does it make sense to you?

Thanks again for your help!

 

[SkipVought]

3) in E2...
[TT]
=INDEX(Sales_Persons,MOD(ROW()-2,COUNTA(Sales_Persons))+1,1)
[/TT]
and copy down thru your list.

4) Turn on AutoFilter

6) Filter on each Sales Person

OR...

4) do a PivotTable on 20 sheets with the Sales Person in the Page Field.


Skip,

_[red][/red]

 

[patrichek]

Hi,
Would you mind explaining in a little more detail, so a newbie can apply your actions?

I understand 1. and 2. I also have E2 pasted. what do you mean by copy down thru your list?

thanks!

 

[SkipVought]

Have you never created a formula for the first row in a table and then copied it and pasted it into the remainder of the column for the rows in the table?

If your SELECT the cell containing the formula, notice the little square in the lower right-hand corner. DOUBLE CLICK on it. It will COPY your formula thru the rows of adjacent data.

Skip,

_[red][/red]

 

[patrichek]

got it working! awesome! thank you!