I have a sales spreadsheet that contains rows of info that is typed in, depending on who has made the sale I want excel to automatically pick up the whole row and show it in the individual salesman spreadsheet. For example column A will show Adam or Ray and the info on that sale will go across to column P.

I have tried "if" strings in the individual salesman spreadsheets but am going wrong somewhere, could you point me in the right direction.
Thanks for your help.
Regards
Lynn

Dear Lynn

Thank you for attending Excel Advanced course with us.

I tried to look at this problem from Advanced filter and Macros point of view. It does seem to work partially but then a knowledge of VBA is required to change the data appearing from one worksheet to another.

A simpler way to solve this problem would be to use V lookup function.

I have attached the file for your reference and hope that it helps you in resolving your query.

I have followed these steps to get the results:

Step 1: On the Sales sheet I have entered five salespersons’ sales data.
Step 2: I created two new sheets “Roger” and “Patrick” starting the headings from cell A4.
Step 3: On Cell A2 on The Roger sheet I created the data validation using the list to enter all the salespersons’ names. I selected Roger from the drop down list!

I didn’t have to do that but the main reason for doing this was because if there are any typos when I manually type the names then the result will not be shown. Especially if I want somebody else entering the names!!

Step 4: Then on Cell A5 of the Roger worksheet I typed in =VLOOKUP($A$2,Sales!$A$2:$D$6,1,FALSE)

You can use the VLookup Function. Please make sure you do enter False in the range lookup box in the function. This will give you the exact match!

Please observe that I have used Absolute ($) referencing so that I can easily copy and paste the function and simply change the column number in the relevant cells.

Step 5: I copied and pasted the function to the cells on the right and changed the column number as everything else was the same hence the absolute referencing ($).

Step 6: I copied and pasted the data validation cell from A2 of Roger sheet to A2 of Patrick sheet and chose Patrick from eth drop down list.

Step 7: I copied and pasted cell range A5:D5 from roger sheet to A5:D5 of Patrick sheet.

It automatically gave me al the result!!

I hope this is clear and has helped you in getting the result you were seeking.
If it has then please mark the question as Resolved and if you are uncertain about anything please press replay and ask from further clarification!!

Kindest Regards

Rajeev Rawat
MOS master Instructor 2000 and 2003

Hi Rajeev,

Thank you for your help. I have a slight problem still. The lookup is only finding the first line in my sales sheet and not any additional lines relating to the same sales person. I have put in the range for example: A2:P15, but it still only finds the 1st applicable line of info.
Appreciate your help
Regards
Lynn

Can someone help me with the last message I sent.

Thanks
Regards
Lynn

Hi Lynn,

I've asked Rajeev to come back to this question at his earliest convenience.

Sorry for the delay.

Regards,
Rich Talbot

Dear Lynn

Sorry for getting back to you so late.

Please check if you are doing one of teh following:

When you copy and paste the Vlookup to teh next cell on the right are you changing the Column Number to 2?

Please remember if you have typed:
=VLOOKUP($A$2,Sales!$A$2:$D$6,1,FALSE)

$A$2 is the cell that is the Lookup Value.
Sales!$A$2:$D$6 is the table array
1 is the Column No. in the table array from where the result gets extracted.
FALSE is to get the Exact Match.

The common error people make is that when they copy the VLookUp function to the next cell they don't change the the column no. so it gives the same value in all the cells that the function gets copied and pasted.

Please ensure that you have changed the Column no. in all the cells that you may have copied and pasted the function to.

Hopefully this should rectify the problem. If you still have a problem then please send me a reply back may be with a precise example with the location of the cells so that I can visually check where the problem might be.

Hope this helps.

Please let me know how it went.

Kindest Regards

Rajeev Rawat