VBA - complex and dynamic data validation system

[pento_thal]

Hi,
I'm trying to create a system that associates a data-validation system to a set of columns.
The data validation should be based on different lists of values, depending on the specific column name.
Each list of values is contained within different sheets of the workbook.
The workbook is composed by:

• a main sheet (base) composed of N columns, each of which should be set entirely as a data validation based on listed values.
  
  
  
  
• a bunch (N) of secondary sheets (A1; A2; A3...) each one with one column (the first one) that contains the list of elements
  
  
  
  
• finally, a third sheet (mapping), which contains a mapping between column and worksheet where get the listed values
  
  

Unfortunately, I'm trying to create a code but I'm continuously failing it. Does anyone have any suggestions on how to proceed?
Thanks

 

[OaklandJim]

FOR ME at least you did not provide enough description for someone to assist. Also, it would be necessary to recreate your workbook if someone is to assist. Might you post a link to the workbook along with another try to describe what you are looking for? Provide a link to the workbook using 1Drive, Box or Dropbox. I think Sharepoint works too.

 

[pento_thal]

FOR ME at least you did not provide enough description for someone to assist. Also, it would be necessary to recreate your workbook if someone is to assist. Might you post a link to the workbook along with another try to describe what you are looking for? Provide a link to the workbook using 1Drive, Box or Dropbox. I think Sharepoint works too.

Thanks for the reply. Sorry for the vagueness of my post, I'll try to better explain my issue.

I would like to create a system that applies some picklists to a specific number of columns present in the main worksheet (in the example named "base").
The list for each of these columns is located into different sheets of the same workbook, which I named A1, A2, A3, and A4 (these lists are updated on a daily basis).

I already created a system that does something like this, by declaring all the ranges before, then declaring all the worksheets, and finally associating each range to a data-validation, linked to a list present as the first column of a specific worksheet.

The problem is that the order of each column may change over time, moreover, new columns could be added.

To solve this issue I would like to create a mapping, present in a new worksheet of the same workbook, called "mapping", with a mapping between the columns headers and the worksheets containing the lists.
The system, by reading the association between headers and worksheets, understands which list to consider to apply the data-validation to each column.

I attach here a mockup, with the expected output:

VBA_data_validation_system.xlsx

base attribute_1,attribute_2,attribute_3,attribute_4 header 2: fixed header 3: fixed

docs.google.com

 

[Mario_R]

Hello. A question:

Why use multiple sheets (from A1 to A4) when that same information could be on a single sheet (one column next to the other)?...

 

[pento_thal]

Hello. A question:
Why use multiple sheets (from A1 to A4) when that same information could be on a single sheet (one column next to the other)?...

Hi and thanks for the reply.
Basically, the reasons are two:

• current sheets are the output of an import of N csv files, managed separately, and then imported by request into different sheets
• The imported csv doesn't contain just the first column, the one to use as a list for the picklist, but many columns (depending on the specific case)

 

[Mario_R]

In the model I attached, the macro is controlled by the information contained in the 'mapping' sheet in its columns A and B:

Sample_2.xlsm
\_________________/​

 

[pento_thal]

In the model I attached, the macro is controlled by the information contained in the 'mapping' sheet in its columns A and B:

Sample_2.xlsm
\_________________/​

Hi, thank you very much for your help, really appreciated it.
It is really close to what I was looking for, I will learn this code to understand if it can match my requirements. In fact, it doesn't properly respect my needs, basically because:

• the headers must be located as the first row, then there will be 2 fixed rows, and finally, the fourth row will be the first row with data-validation
• the mapping should be used just as reference, to understand which sheet to use. It shouldn't regulate the column creation (that will follow a different logic)
• I would like to manage it not as a "table" so that I can manage columns with more flexibility

 

[Mario_R]

Only when users are very experienced in using Excel do they understand that just as important as the data is that the titles of the data must "exist" and stand above the data itself.

That's because different Excel tools require them in that position and not "any other".

 

[pento_thal]

Only when users are very experienced in using Excel do they understand that just as important as the data is that the titles of the data must "exist" and stand above the data itself.

I got your point. Unfortunately, the worksheet structure is fixed because the same sheet will be uploaded to a management system that needs to read the 3 columns before the data.