How to have data automaticly transfered from sheet 1 to sheet 2 when its labeled "IV"

[jawaune]

Hello Microsoft Excel experts. I have a question and I really hope that you can help me out. I would appreciate it so much. My business requires me to dial names and phone numbers all day from a list of leads in Microsoft Excel. I use verious disposition codes for each phone number I dial such as...

WN = wrong number
NQ - Not Qualified
IV = Invite
SA - Sale
DNC = Do Not Call
UA = Unavailable
AM = Answering Machine

What I would like to do is apply a function to the disposition column so that when I label a lead as "IV" the information will automatically be copied to sheet 2.

Please click on this link to get a microsoft excel list of leads that I've already dialed on and has dispositioned applied to it so that you know exactly what I'm talking about.

[NBVC]

I hope those are not real phone numbers and real names.... I will delete the link in case they are...

In A2:

Please Login or Register  to view this content.

confirmed with CTRL+SHIFT+ENTER not just ENTER and copied down to how far you want...

You may need to adjust ranges if you think you will need more than 1000.. then reconfirm with CSE keys and copy down again.

In B2:

Please Login or Register  to view this content.

copied down and across.

[jawaune]

The column that I have the dispositioned applied to is column "E" so would I be able to use this function for the entire column?

[NBVC]

Not unless you are using XL2007, otherwise you need defined range... like E2:E1000, but you can make it as big as you want.. with just a caution that the bigger the range the more processing time is taken.. so try to limit to what you think will be the max size.

[NBVC]

If you want to use whole column references, then you can but you will need to add a helper column in the original sheet to help achieve the goal.

[jawaune]

Well.....I am using EL2007. The problem wth the forumula you provided to me didnt work and I cant place it in A2 because there is already data in that cell . When I cut and pasted in there it delated the data and just put numerical value of 0 in the cell and none of the rows dispositioned as IV were transfered to sheet 2. I think it would be better if you provided me a formula that I could put in an empty cell which doesn't have data applied to it. Can you give me a formula like that so that I can copy and paste it again?

[NBVC]

Well, I deleted the sample, but in that sample A2 of the sheet you wanted the data to go to was empty and that is where I applied the formula for you to copy/paste and confirm.

Can you re-attach the sheet with confidential information edited?

Also, if you are using XL2007, you should update your profile to say so, so that formulas can be catered as necessary.

[NBVC]

Note also, that after you copy/paste the formula, you have to activate the cell and hold the CTRL+SHIFT keys down and hit ENTER. A pair of { } brackets will surround the formula, and then you can copy it down the column.. you only need to do this for this formula.. the other formula is regularly ENTERed

[jawaune]

I tried it the way you suggested but it still doesn't work. I put the code in A2 and it just returns the person's name from cell A2 of sheet 1 which isn't evn a "IV" dispositioned row and when I put the other code in B2 as you requested it just returns a bunch of numbers "9383886" to be exact

[jawaune]

oops I didnt read your previous post about the brackets, let me try that.

[jawaune]

so are you saying the a2 formula gets surrounded and brackets and the b2 formula does not?

[NBVC]

The bunch of numbers should be the phone number associated with the name pulled in column A.. you may need to format as Phone Number per your original sheet.

Ok, I must apologize.. that I did have a couple of screw-ups in the formula... the formula (revised for XL2007) is now:

Please Login or Register  to view this content.

confirmed with CSE keys...

See attached...

[jawaune]

Its working!!!! However there is one adjustment I need your help with. As you can see in sheet one I have the area code in a seperate column so I will need the copied data to include the area code column as well. can you tell me how to edit that?

[NBVC]

Are you looking to put them in the one cell or separately?

If separately, then insert a column and apply the Vlookup() formula...

If concatenating in one cell, then use formula:

Please Login or Register  to view this content.

copied down

[jawaune]

Thank you very much! I got exactly what I wanted. You can mark this one as solved. I just have 1 last question. Can you please give me any resources where I could go and learn this stuff? Because even though I copied and pasted and it works I dont understand the syntax of the code. I am a DIY type of person so I'd like to understand it.