98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel Training and help » excel training - Excel
excel training - Excel
Resolved · Low Priority · Version Standard
Wed 2 Jan 2008, 10:18Delegate Lynn said...
Lynn has attended:
Excel Advanced course
Excel
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
Thu 3 Jan 2008, 13:46Trainer Rajeev said...
RE: Excel
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
Mon 14 Jan 2008, 14:08Delegate Lynn said...
RE: Excel
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
Mon 21 Jan 2008, 10:13Delegate Lynn said...
RE: Excel
Can someone help me with the last message I sent.
Thanks
Regards
Lynn
Tue 22 Jan 2008, 09:24
Edited on Tue 22 Jan 2008, 09:24Trainer Rich said...
RE: Excel
Hi Lynn,
I've asked Rajeev to come back to this question at his earliest convenience.
Sorry for the delay.
Regards,
Rich Talbot
Thu 24 Jan 2008, 23:25Trainer Rajeev said...
RE: Excel
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
Training information:
See also:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Entering text in Multiple WorksheetsIf you have a number of worksheets in a workbook that require the same information (data or tables) on each worksheet, this can be done as follows: |
