How do I link Dropdown List (data validation) with filtering data from a table?
Hi, Im going crazy trying to figure this one out.
Does anyone know how to make a data range get automatically filtered based on what you select in a dropdown list?
Preferably, without using VBA or Macros, because I dont understand such advanced settings.
It's pretty urgent, so any help will be highly appreciated! thank you!
Hi, welcome to the forum
Do you have a sample workbook that we could look at, so we can see what you are working with - and what you want?
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Well, actually Im using examples now, Im at home and I need it for work tomorrow. But here's an example file:
excel filter data validation example.xlsx
I basically want to run a subtotal of all the number columns PER person name. So I was thinking of a subtotal function. But I would need a way to make an automatic filter of the data when I pick a name from the list.
Any ideas?
The names in your sampl list only appear once. If that is representative of all your data (all unique names), then try this in B2, copied across...
=VLOOKUP($A2,$A$6:$G$31,COLUMN(),0)
If the names will repeated, then perhaps give a more representative sample?
edit: Actually, if the names will repeat, then use the VLOOKUP() for the 1st 2 columns (format as needed), then use this for the other columns to run the sum...
=SUMIF($A$6:$A$31,$A$2,D6:D31)
Yes sorry I sent you the one with no repeats. But the idea is that there could be one name and many entries and I want it to sum all of them per person, then throw the subtotal when I pick the name from the dropdown list.
Im not sure I understand the second function (sumif). What's the criteria?
C2=VLOOKUP($A2,$A$6:$G$31,COLUMN(),0)
D2=VLOOKUP($A2,$A$6:$G$31,COLUMN(),0)
E2=SUMIF($A$6:$A$31,$A$2,D6:D31)
copy E2 across
Adjust the ranges as needed
Ok I applied SUMIF and it's working !! But only for one column of the numbers, I dont kknow why. How could I do the same but to sum all the values in the four columns of numbers for the person selected? (I did: =SUMIF(A6:A31;A$2;E6:G31) ) Thank you!
Last edited by Trishux25; 07-29-2015 at 09:34 PM.
OK, if you want 1 total for all, try this...
=SUMPRODUCT(--($A$6:$A$31=$A$2)*(D6:G31))
It worked!!! Thank you so much!!
Happy to help.
If this answered your question, please take a moment to mark the thread as "solved" - it helps keep things neat and tidy lol, and consider adding the reputation to those that helped (see points 2 & 3 below my 1st post to you)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks