• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How can I make Data Validation and 'Advanced Filter' dynamic ???

J

James Perry

Member
I have a worksheet that contains rows of data.
Column A has rows of sales data for a month.
Cell A1 has the label Sales_Rep.
Col B1 has the label Sales_Value.
Col C1 has the label Mode_Of_Payment (Cash, Check, Card )
I do a 'remove duplicates' on column A that contains the names of sales persons.
That leaves only the unique names.
Then I do a Data Validation on the unique names.
Now I want to do a 'Advanced Filter' to display the 'sales rep and sales values' for each sales person for the month.
So I link the name on my criteria box to the DV cell.
The report works fine with the first name i select on the DV.
But when I select another sales name in the DV box the name in the criteria naturally changes but the output does not change.
That is my question ???
Million thanks gurus for ur valuable time and effort.

Sample as below. Hope that helps.

Sample as below -
myCriteria:
SalesRep SalesAmt MOP
Rick
Output as below:

SalesRep SalesAmt MOP
Rick 5000 Cash
Rick 2000 Credit
Rick 7000 Card
 
Last edited:
The table is a much under used fearture, but very useful and powerful when used and understood.


.
 
Hi James ,

I think your reply to Misra is , shall we say , a little brusque.

Hui has put up a notice requesting posters to attach a sample file with their questions where ever possible ; even if the data is confidential , it is a simple matter to replace it with random data , reducing the size of the file from its hundreds or thousands of rows to less than 20 rows , which may suffice in most cases , unless the problem itself is dealing with large file sizes.

With the right intentions , preparing a sample file should not take more than 10 minutes.

Without a sample file , those who are interested will all have to prepare the same sample file ; either the question is such that answering it without an open workbook is difficult or those who wish to help are not such experts that they can just visualize everything mentally and suggest a solution.

Those who answer questions on any forum certainly have an inclination to help , but to expect that they also have lots of spare time at their disposal , is I think too much to expect.

Narayan
 
James

If attaching a sample file, we don't care about the contents
So simply replaces names with AA, BB, CC etc
With numbers multiply them by some random value

The layout/structure and data type is what is important when trying to assist people with there problems
 
You must log in or register to reply here.
Back
Top