Pull data based on a drop down selection

Hoping someone can help, i'm a bit inexperienced with macros.

Basically what i want to do is have a user select an option via a drop down list. I need a macros that will read the selected option then pull data from a corresponding worksheet. The selected option will be in column A and wherever it appears the macros would automatically pull that whole row i.e. the the corresponding values in column b, c, d, etc. Is this straight forward?

Hey mate,

1. It's a list of sales managers.
2. I've just entered the names into a list and built the drop down from that list via data validation.
3. Yes it will appear more than once. Column A will list the managers out while column B will have customers and then the following columns will have sales figures by date.
4. I want to pull it into the next tab so that it lists out the chosen sales manager and their customers with corresponding sales. Would like it in a crosstab format with dates along the top.

Probably a silly question but how do i attach items?

Sorry, I meant attach files so you could see what I meant. I think i've managed to attach it. Basically i want to choose a person's name via the drop down and then have that persons customer plus their sales by day populate in the next sheet exactly as it appears in the first sheet. Hope that makes sense.

Thanks

No problem, thanks for your help mate.

John

Hi John,

Sorry i probably haven't explained this too well. This is just an example of what I have, there are a lot more managers/customers than this. I've added a bit more data and alterd the spreadsheet a bit to maybe explain it better. Sheet1 will have the data dump (which will be a lot larger than what's there). Ideally when a manager goes to use it, they'll choose their name from the drop down in Sheet2 and basically what i need to do is figure out a macros that will read cell D1 in sheet2, search column D in sheet1 and return that row (i.e. the customer and figures in that row). From this i can set up graphs that will populate with the data for the selected manager only. I've pasted the relevant data into sheet2 and added a quick graph to show what it would look like ideally. Appreciate your help.

John

Hi John,

thanks for that. I've just copied it into my spreadsheet and when i've run i've received a run time error '9'. it says subscript out of range. It refers to the line below.

Sheets("Sheet3").UsedRange.Offset(2, 0).ClearContents

Could you explain what this means please? I changed it to "Sheet3" as this is where the orginal data dump is located.

Thanks,
John

Sorry about that, still new to this. I've managed to get it working now, it was to do with the tab names. I'll let you know if i get stuck again.

cheers,
John

Hi John,

Thanks for your help on this one, the code you gave me earlier worked perfectly. Hoping you can help me out with something else i've got that follows on from the earlier piece.

On a new tab (Workings), i've set up some formulas that pull from the data in sheet2. Obviously these workings will change depending on the sales manager selected which will in turn change the Workings tab. I have some graphs set up on the last tab that feed off the Workings tab. What i want to be able to do is set up a macros that will automatically pull the data from the workings tab and plot it in seperate graphs.The data dump that feeds the whole file will always have a different time period so if i just add graphs like i have now, i'll always have to go in and adjust the range for each graph. Ideally i could have a macros that plots the graphs and automatically works out the range. Is this possible? I've attached the original file with new tabs to give you an idea of the desired outcome.

Thanks,
John

Hi John,

No worries mate, you've been a massive help already. Appreciate it.

John

Hi John,

In the last file you attached, what alteration would i make to the code if i wanted to have the dropdown in another tab? i.e. have the sales manager drop down on sheet 3 instead.

Thanks,
John

Hi John,

Sorry, I don't think i explained it properly. What i was after was to have the data remaining on sheet2, with the dropdown on sheet3. At the moment, i think you have the dropdown on sheet3 and it's changing the data on sheet3. The reason i'm trying to do it this way is because i want to hide sheet2 (with the data) and have a page with graphs (sheet3). Ideally when a user opens the file, all they'll see is a page of graphs with a dropdown at the top of the page. When they change the dropdown, the data will update in the background and feed the graphs. Is this possible by just tweaking the code?

Thanks,
John

thanks so much, really appreciate it

I know that this post is kind of old, but I think this answers part of my question I had today. Here is my situation:

I have a list of 250 agents. Next to each agents name is the name of there manager. There are about 13 managers. Column A has the agents, Column B has the TM. Basically, using data validation I want the following: When I choose a TM from the drop down box, I want the agents assigned to them to populate in another drop down box. I think this will do it it somewhat, I just want to be able to understand how this works so I can use it for my function.

This file was helpful in a project I was working on, but I am wondering if there is a way 4 criterias can be used? Any filter on these criteri would bring back data corresponding with the filtered criteria. If so how can I modify the code in the attached file to address this.

Hello remysteve262, and welcome to the forum.

Unfortunately you have inadvertently broken one of the forum rules. Please read the following and make the necessary change. Thanks.

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.