L
lasergifts
The goal I'm trying to accomplish is to associate a sales rep from one
sheet in a 4 page spread sheet with a sales lead on another sheet by
zip code, and paste the Reps first name last name and fax onto the
sales lead sheet. I have a few pieces but can't seem to put them
together (my Excel knowledge is limited). Here's what I have so far:
sheet 1 = sales leads
sheet 2 = sales reps
sheet 3 = sales rep groups (groups that manage individual reps)
sheet 4 = zip codes
1. I need to match the sales lead zip with a zip code list to determine
which rep gets the lead. I know this formula will compare, but don't
know if it will find the exact one...
=OR(EXACT(L2,Zip Codes'!B2:B43028))
2. Next I need to copy the matched zip's rep code (Zip Codes'!Bx) where
Bx is the matched rep code
3. Then I need to compare the matched rep code with the sales rep
sheet.
=OR(EXACT(Zip Codes'!Bx,Reps'!A2:A87))
4. When found (Reps'!Ax), where Ax is the matched rep code, I need to
copy the reps first and last name in that record and paste it into the
leads sheet...
=CONCATENATE (Reps!'Bx,Reps!'Cx)
...don't really know how then to paste this result into AC2 of the
sales lead sheet.
then I need to paste the rep fax (Reps!'Ix) into AD2 of the sales lead
sheet.
I know this is a lot...but hopefully one of you Excel geniuses will
have an idea.
I've attached a copy of the sheet I'm working on with only a few
records. The formula won't work with it because the records won't match
up, but it will give an idea.
Thanks...I appreciate your help!
Attachment filename: sales lead example.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=486636
sheet in a 4 page spread sheet with a sales lead on another sheet by
zip code, and paste the Reps first name last name and fax onto the
sales lead sheet. I have a few pieces but can't seem to put them
together (my Excel knowledge is limited). Here's what I have so far:
sheet 1 = sales leads
sheet 2 = sales reps
sheet 3 = sales rep groups (groups that manage individual reps)
sheet 4 = zip codes
1. I need to match the sales lead zip with a zip code list to determine
which rep gets the lead. I know this formula will compare, but don't
know if it will find the exact one...
=OR(EXACT(L2,Zip Codes'!B2:B43028))
2. Next I need to copy the matched zip's rep code (Zip Codes'!Bx) where
Bx is the matched rep code
3. Then I need to compare the matched rep code with the sales rep
sheet.
=OR(EXACT(Zip Codes'!Bx,Reps'!A2:A87))
4. When found (Reps'!Ax), where Ax is the matched rep code, I need to
copy the reps first and last name in that record and paste it into the
leads sheet...
=CONCATENATE (Reps!'Bx,Reps!'Cx)
...don't really know how then to paste this result into AC2 of the
sales lead sheet.
then I need to paste the rep fax (Reps!'Ix) into AD2 of the sales lead
sheet.
I know this is a lot...but hopefully one of you Excel geniuses will
have an idea.
I've attached a copy of the sheet I'm working on with only a few
records. The formula won't work with it because the records won't match
up, but it will give an idea.
Thanks...I appreciate your help!
Attachment filename: sales lead example.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=486636