Copying data from one sheet to another based on drop down item selected.

[VGH11]

Hi there,

Im trying to create a master sheet of all sales. Then, based on what sales person is selected in the dropdown list, I would like the rest of the data in that row to be copied to the next available row on that sales persons sheet. 

So, if in "I", "John Smith" is selected, I would like the data in row 4: A-H (date, customer, equipment, quantity) to be copied to the next available row in the "John Smith" sheet; if "Jane Doe," copied to her sheet; If "Joe Public" copied to his sheet.. and so on. 

(see attached) 

[#2]

@VGH11 

I think we're going to need a bit more information on what happens next, in order to make a solid and effective recommendation for you.

If I'm not mistaken, the Power Query capability would be very helpful here, though there may be (as often is the case) more than one way to accomplish this. But what we need to do is distinguish between INPUT and OUTPUT.

The input phase of things is what you're doing with your "Master" sheet. So go ahead and fill in a few more rows with random sample data.

The output is what you're doing with the others, but my guess is that you only need ONE output sheet, and you can use the Power Query and other data extraction or sifting functions to pull the data for John Smith or Jane Doe (etc) into that single output sheet by using their name in a dropdown box in cell A1 of the output sheet.

there remain a few more questions: where does the data for "Dealer Dollars Used" come from? How is it used in the output sheet?

If you could fill in some more rows on your master, and then give a sample of what the output would look like for John Smith, say...including what happens to calculate commission or whatever else you are doing with this, that would be helpful.

[#3]

@mathetes 

Thank you for your reply. 

However, I do not believe we are on the same page. 

The goal is to have a master sheet where the sales manager or secretary can input all sales and assign them to correct salesmen. That data is then copied to the appropriate salesperson's sheet. 

Nothing needs to happen to dealer dollars, as that would be manually inputted by the individual salesman and only applied to him/her. The current commission rate is automatically generated based on the total units sold (h25) using an IF function in H37. 

I am just looking for a way to copy the date, customer, equipment sold, and unit quantity data to the appropriate salesperson's sheet for the sake of continuity. 

[#4 · Accepted solution]

@VGH11 

Oh, we're definitely not on the same page. I was suggesting a different way of thinking about this. Rather than having multiple output sheets--although maybe there's a reason for that--I was suggesting you think about this in a different manner altogether.

In any event, I'm attaching an example of how the FILTER function could extract your data based on whichever name you put in cell A1 of the output sheet.

You would be better served by not doing so much combining of cells as you've done on the Master sheet in yours... you'll see that in my sample example all data in a single column (you had Customer name in one cell, but that one cell combined several columns. Let your output be formatted nicely, but your input document is for data collection and doesn't need to be sexy.

I hope this helps point you in the right direction. Notice that a single formula (copied to all the cells) does the extracting on the Output sheet.

[#5]

@VGH11 

Use FILTER, if you have it.

[#6]

Thank you so much! That is exactly what i needed. Idk why I was trying to make it so difficult lol