How to get dynamic Dropdowns based on the header cell such that the dropdown options are from range, named header cell, in another Source Workbook?

[RajK2005]

Hello everyone,

I am struck with this problem since yesterday and have been unable to find any solution despite all the web searches. Please help me if it is indeed possible.

To better explain the situation:

• We have multiple templates for different product types (400+). We share these files with our clients to fill. Some column headers are common among all templates and some are unique to each template. On an average, each template will have 60+ columns.
• There is no order for the columns in each of those templates since the number of columns are dynamic.
• The values to be entered in most column are restricted to certain values. Others are freetext.
• There is another workbook called "SourceData" where you will find acceptable values for each of these column headers. There are in total around 250+ unique column headers in all 400+ templates. Each of these columns is a named range (name being the header value).
• I would like each cell below any column header in any template to have a dropdown. The dropdown should show options from the SourceData workbook based on the column header.
• The columns for which freetext is allowed, there won't be a column for those in the

Here's a link for 2 sample files: SourceData and Product001
When the client opens both the workbooks (SourceData and Product001) and selects B2 row (Gender) in Product001, I want him to have the dropdown options from Column D (Gender) in SourceData.xlsx. When he moves to C2 (Seasonality), it should show dropdown options from Column E (Seasonality) in SourceData.xlsx. When he moves to Column H (Age Group), it should show dropdown options from column C (Age Group) in SourceData.xlsx.

The only way I know to do is creating dropdown lists one by one. That would mean creating (60*400=) 24000 dropdown lists minimum, one by one.
I know about Dependent Dropdowns and using INDIRECT Option but that would require the named ranges be with-in the same workbook. That would again require me to either copy sourcedata to 200+templates or define 200+ ranges in each of the 400+ templates referencing the sourcedata.

The only experience with VBA is by copy pasting useful VBA codes from web but have no working knowledge of it. I cannot find any way to do it quickly. I need it in another 24 hours. I use Office 365.

Also, should I replace all space in column headers with Underline to make it easier?

Any help is extremely appreciated.

 

[Akuini]

I need it in another 24 hours.

It's been over 24 hours since you posted the question. Do you still need it?

 

[RajK2005]

It's been over 24 hours since you posted the question. Do you still need it?

Yes, if possible.
For now, I am using an alternative way by copying the source lust to every template but it isn't really efficient and still working out many issues. Takes a lot of time.

if you have any solutions, It would be really helpful.

 

[RajK2005]

It's been over 24 hours since you posted the question. Do you still need it?

The way I am doing it right now is copying the sheet from sourcelist to every template and adding dropdowns with-in each workbook. Thankfully, I found a macro online to copy the source sheet to each of the 412 templates and I managed to improvise it to include dropdowns based on the column headers (Macro Recording).

It's extremely time consuming, very slow and most importantly, adds a dropdown even to the columns for which no data exists in source sheet. It shows a blank dropdown.

This might be misleading to clients that those attributes are restricted even though they are allowed to enter any value in those columns. For example, the description column. They can enter anything in that column. So there is no column called "Description" in the sourcelist.

But the VBA I am using below adds a dropdown even there as well.

    Range("A3").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=Indirect(A$1)"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = False
        .ShowError = False
    End With

So I will have to either find a way to fix it so only those column headers which exists in the 1st row in the sourcelist has a dropdown or hope I get an answer for original post which I think will be more efficient than my method.

Hope I am not confusing you. English isn't my first language and might be messing up my explanation.

 

[RajK2005]

It's been over 24 hours since you posted the question. Do you still need it?

Both the Product Template and the SourceData workbooks are here:

Excel Help - Google Drive

drive.google.com

It seems I accidentally pasted link for only the Product template in my original post. Apologies.

Here is the overall VBA I am running on SourceData to copy it's only sheet to each of the 412 templates and then add dropdowns to every column in existing sheets of those templates.

Option Explicit

Public Sub AddMasterListToAllWorkbooks()
    
    Dim sourceSheet As Worksheet
    Dim folder      As String, filename As String
    Dim destinationWorkbook As Workbook
    
    Dim templatename As String
    Dim TemplateSheet As Worksheet
    Dim rng         As Range
    Dim tbOb        As ListObject
    
    'Worksheet in active workbook to be copied as a new sheet to the workbooks
    
    Set sourceSheet = ActiveWorkbook.Worksheets("SourceData")
    
    'Folder containing the templates
    
    folder = "C:\Users\Username\Downloads\Templates\Drive\"
    
    filename = Dir(folder & "*.xls", vbNormal)
    While Len(filename) <> 0
        Debug.Print folder & filename
        Set destinationWorkbook = Workbooks.Open(folder & filename)
        sourceSheet.Copy After:=destinationWorkbook.Sheets(1)
        
        'Format Template as a Table
        
        templatename = Mid(destinationWorkbook.Name, 1, InStr(1, destinationWorkbook.Name, ".") - 1)
        
        Set TemplateSheet = destinationWorkbook.Worksheets(templatename)
        
        Sheets(templatename).Select
        
        Set rng = Range(Cells(1, 1), Cells(Range("A1048576").Row, Range("XFD1").End(xlToLeft).Column))
        
        Set tbOb = Sheets(templatename).ListObjects.Add(xlSrcRange, rng, , xlYes)
        tbOb.Name = "DynamicTable1"
        tbOb.TableStyle = "TableStyleMedium2"
        
      
        
        'Add Dropdowns
        
        Range("A3").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=Indirect(A$1)"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = False
        .ShowError = False
    End With
        
        destinationWorkbook.Close True
        filename = Dir()        ' Get next matching file
    Wend
    
End Sub

If the solution for my original post doesn't exist or is too complicated, the above VBA will also work if you know how to improvise it to avoid adding dropdowns to those columns where the column header doesn't exist in source data. In each template, there will be 30-40 random columns for which no values would exist in the sourcedata sheet.

 

[Akuini]

1. So you have hundreds of workbook as template and 1 workbook "SourceData"?
2. You said this:

When the client opens both the workbooks (SourceData and Product001)

but why then you said:

Here is the overall VBA I am running on SourceData to copy it's only sheet to each of the 412 templates

I thought you want data validation in each template workbook to get the list directly from workbook "SourceData"?
It means that if you change data in workbook "SourceData" then you don't need to recopy the data to the template workbooks.

3. Is it ok if the template workbooks has macro in it, so they will be .xlsm instead of .xlsx?

 

[RajK2005]

1. So you have hundreds of workbook as template and 1 workbook "SourceData"?
2. You said this:

but why then you said:

I thought you want data validation in each template workbook to get the list directly from workbook "SourceData"?
It means that if you change data in workbook "SourceData" then you don't need to recopy the data to the template workbooks.

3. Is it ok if the template workbooks has macro in it, so they will be .xlsm instead of .xlsx?

1) Yes, I do have 1 sourcedata workbook and 412 templates.

2) The original idea when I made the post was that I will inform clients to open both SourceData and ProductTemplate from the same folder. They will fill productTemplate using dropdowns which will be sourced from SourceData.

However, since I didn't get any response here and I had no idea how to proceed, I decided for a temporary alternative until I find a better solution.

In this temporary solution, I am copying SourceData to each of the 412 templates and adding the Data Validation using the VBA I wrote above.

When there are changes in the SourceData, I figured I will just recopy the data from SourceData to the 412 templates every 2-3 days whenever I observe changes in the source data. It isn't frequent. Maybe once or twice a week. I was able to figure out a VBA for that with some tinkering of the VBA to copy the sheet: It deletes the existing sheet, deletes all the names and then copies the Sourcedata again. For all 412 templates.

That was the best solution I could come up with. Even if it's time consuming, it will work, if I just find out how to avoid dropdowns for cells where there is no data and the column doesn't exist in sourcedata.

3) Macro-Enabled Workbooks will not work because our clients aren't well experienced in Excel and some use really old versions so might give compatibility issues.

 

[RajK2005]

To give you an illustration of my issue with my temporary solution,

Here's my product Template

Here's my SourceDate. As you can see, all the columns in the above image except the ones I have selected (Columns B, I J) are indeed present in the below SourceData Sheet.

I have named a range for each of the column header in the below sheet and it is named based on the column header.

When I run the previously mentioned VBA which adds dropdowns, it looks at the Value in the top row of each column in the template and looks up a range named exactly same (Indirect function) and then adds a data validation dropdown with only values from the respective range allowed in that column.

But SourceData doesn't have a ranges Name, Sales Tax And Description. So Columns B, I J in the template have blank options in the dropdown. I want to somehow remove these dropdowns. These aren't fixed and change from template to template. Some columns will be from sourcedata, others will be free text where they can enter any value.

This means, the clients will see a dropdown misleading them to think it's restricted value or worse when they enter any data by copying and pasting, there will be a Green indicator in each cell showing error.

The whole purpose is to give them what info to enter and if they copy paste from their own database, highlight the errors immediately so they can rectify before they share the data with us. But if we tell them you have to ignore some errors because we couldn't improve, then they are gonna give everyone of them and it will be useless.

You have any instructions on how to achieve that. My boss will be asking me on update and while he might not create a fuss about this, he definitely will ask in another 12 hours (Last weekday update) and I want to fix this one last issue before I present it.

If however, you have a solution based on my original request, that would also be great. Either way, as long as I get dropdowns only to the columns for which there is data, it will be great.

 

[Akuini]

1. There are no named ranges in the original template ('Product001.xlsx') but there are named ranges in the new template ('Product001 (with SourceData after I ran my VBA).xlsx')
How did you create all the named ranges in the new template ?
The code in post 5 only shows it copied the Source worksheet to the original template, but not the named ranges.
When I tried the code on Product001.xlsx it stopped at this line:

        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=Indirect(A$1)"

it because there are no named ranged in the original template .

2. In the original template the data is not in actual table (just a range) but in the new template the data is in actual table. Is that what you want?
If yes, than I suggest to create the table in all original template first, so the code to create data validation doesn't need to deal with creating table. I can write the macro to create the table in all original template.
The reason I suggest this is because in the future when you change data in the Source workbook, if you rerun the code in post 5, it will try to format a table as table. It will raise an error.
So we have 2 codes:
a. Code to create the table in all original template. You only need to run it once.
b. Code to copy data from source to template, create named ranges & create data validation

3. I suggest that the original template & the new template are different file. For example: the original template is 'Template-Product001.xlsx' then the new template is 'Product001.xlsx'. Because it is easier to write a code that will be rerun only on the original template than on both original and new template.

 

[RajK2005]

1. There are no named ranges in the original template ('Product001.xlsx') but there are named ranges in the new template ('Product001 (with SourceData after I ran my VBA).xlsx')
How did you create all the named ranges in the new template ?
The code in post 5 only shows it copied the Source worksheet to the original template, but not the named ranges.
When I tried the code on Product001.xlsx it stopped at this line:

        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=Indirect(A$1)"

it because there are no named ranged in the original template .

2. In the original template the data is not in actual table (just a range) but in the new template the data is in actual table. Is that what you want?
If yes, than I suggest to create the table in all original template first, so the code to create data validation doesn't need to deal with creating table. I can write the macro to create the table in all original template.
The reason I suggest this is because in the future when you change data in the Source workbook, if you rerun the code in post 5, it will try to format a table as table. It will raise an error.
So we have 2 codes:
a. Code to create the table in all original template. You only need to run it once.
b. Code to copy data from source to template, create named ranges & create data validation

3. I suggest that the original template & the new template are different file. For example: the original template is 'Template-Product001.xlsx' then the new template is 'Product001.xlsx'. Because it is easier to write a code that will be rerun only on the original template than on both original and new template.

1) Ah sorry, the ranges are indeed named in the file I have. But not in the one I uploaded above.

So when I run this to copy the SourceData to each of the 412 templates, the existing named ranges are also copied to each of the templates. So it works. I should have mentioned.

Thing is I don't want to share any confidential data (New Job). So I created a sample file but didn't recreate it exactly. Sorry. Updated now.

2) Yes, I do prefer it if it is formatted as a Table. Helps our clients to enter data more easily.

As for future changes, It won't be rerunning the same code. I did encounter that error many times because the table already exists. So the code I created to update the data later on is this:

Option Explicit

Public Sub UpdateMasterListAllWorkbooks()
   
    Dim sourceSheet As Worksheet
   
    Dim folder      As String, filename As String
   
    Dim destinationWorkbook As Workbook
   
    Dim mySheetName As String
   
    Dim MyName      As Name
   
    'Worksheet in active workbook to be copied as a new sheet to the workbooks
   
    Set sourceSheet = ActiveWorkbook.Worksheets("SourceData")
   
    'Folder containing the workbooks
   
    folder = "C:\Users\Username\Downloads\Templates\Drive\"
   
    filename = Dir(folder & "*.xls", vbNormal)
   
    While Len(filename) <> 0
       
        Debug.Print folder & filename
       
        Set destinationWorkbook = Workbooks.Open(folder & filename)
       
        'Delete existing named ranges
       
        For Each MyName In Names
           
            destinationWorkbook.Names(MyName.Name).Delete
           
        Next
       
        'Delete the existing SourceData
       
        mySheetName = "SourceData"
       
        Application.DisplayAlerts = FALSE
       
        destinationWorkbook.Sheets(mySheetName).Delete
       
        'Copy Updated SourceData (including the named ranges).
       
        sourceSheet.Copy after:=destinationWorkbook.Sheets(1)
       
        Application.DisplayAlerts = TRUE
       
        'Close Workbook
       
        destinationWorkbook.Close TRUE
       
        filename = Dir()        ' Get next matching file
       
    Wend
   
End Sub

It doesn't do any of the data validation, templates or all that stuff. Only deletes the existing SourceData sheet (and the named ranges), then copies the new SourceData sheet (and the named ranges).


So I have 2 codes:

a. 1st Code to copy SourceData sheet (and named ranges) from source to template for the 1st time, format as a table, create data validation dropdowns.

b. 2nd Code to delete the existing SourceData sheet (and named ranges) in the template and copy the updated SourceData sheet (and named ranges).



3) If it's easier, then I am fine with that.



Also, do you have any suggestions on how to fix this:

Sub selColumns()
   
    Dim ws As Worksheet: Set ws = ThisWorkbook.ActiveSheet        'better use Thisworkbook.Sheets("SheetName") / or ActiveWorkbook / or specific workbook
    Dim lCol As Long: lCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column        'Get the last column
    Dim R As Long, C As Long
   
    Dim arrHeadingsToSelect() As String
    arrHeadingsToSelect = Split("gst,brand,hsn", ",")        'Add more as needed
   
    For C = lCol To 1 Step -1        'Loop from last column to first
        For R = LBound(arrHeadingsToSelect) To UBound(arrHeadingsToSelect)        'Loop through each of the headings in the above array
            If ws.Cells(1, C) = arrHeadingsToSelect(R) Then        'If there is a match
            ws.Cells(1, C).EntireColumn.Select        'selects the column...
            With Selection.Validation
                .Delete
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                     xlBetween, [B][I]Formula1:="=indirect(arrHeadingsToSelect(R))"[/I][/B]
                .IgnoreBlank = TRUE
                .InCellDropdown = TRUE
                .InputTitle = ""
                .ErrorTitle = ""
                .InputMessage = ""
                .ErrorMessage = ""
                .ShowInput = FALSE
                .ShowError = FALSE
            End With
            Exit For        '...and move to check the next one
        End If
    Next R
Next C

End Sub

From some googling, I found a way to delete columns with headers (gst, hsn, brand... 233 in total). I modified it to select those columns instead. I was hoping to add data validation after selecting the column and repeat for all possible headers columns in each template. I can add it to my 1st code. This will help avoid drop-downs in cases where there is no data. This might be time consuming the 1st time as it will run this for 233 possible headers from SourceData Sheet.

While I manage to select the columns, I don't know how to implement the INDIRECT FUNCTION here. It correctly selects the column but fails at Indirect function. I want to set the Indirect function to the column header. If I use something like A1. It executes successfully but all the dropdowns will be based on A1 column.
I don't know how to direct it to use the dropdown based on the column header. Any tips?