• 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.

Dynamic Data validation with VBA doubt.

J

Jagdev Singh

Active Member
I have found many sites for dynamic data validation in VBA code. I am confused how it will be useful in my case. I have Data in a RAW_Data sheet. The data to be used in data validation in is column J, but column J consist of many duplicate entries in it.

Currently what I am doing is copy the entire column J in a new sheet remove duplicate entries and then use data validation option in the same sheet. I am looking to make this task automate. Also, the data in column J keeps on changing as in new entries keep on getting added in it. Could you please guide me to a path to avoid doing manual task of data validation?

Regards,

JD
 
Hi JD,

Check out this example, which uses a PivotTable to calculate the validation list. You just need to refresh the PivotTable to update your list. You can do this manually, or setup an worksheet event type macro that would do the refresh. Let me know what you think.
 

Attachments

Hi Luke

Nice approach, but I am not able to implement this logic in my workbook. First I created the Pivot table in a sheet3 and then used name manager "dvlist" and in the place of Refer used the formula you have added in the sheet.

Then I when to the cell where I want the validation and click the option of data validation and in the place of the Source I added "=dvlist". It keeps on throwing the following Error msg "The Source currently Evaluates to an error. Do you want to continue"

Please let me know what I am doing wrong here.

Regards,
JD
 
I would suspect that you didn't setup the named formula for "dvList" correctly.

First, make sure that your PivotTable start in row 1, like mine does, with no Grand Total. So, it should only have the Row label and then the items you want.

Next, the dvList formula should be:
=Sheet3!$A$2:INDEX(Sheet3!$A:$A,COUNTA(Sheet3!$A:$A))
with the sheet names/column matching up to where you place the PivotTable.
 
You must log in or register to reply here.
Back
Top