来自其他工作表和列的动态下拉列表(偏移/索引?) [英] Dynamic dropdown from other sheet and column (offset/index?)

问题描述

我有一个 Excel 文档，其中包含 2 个工作表、1 个导入工作表和一个数据工作表.导入表 B 列中的动态下拉菜单应取决于导入表 A 列中选择的值.

I have an Excel document containing 2 sheets, 1 import sheet and a data sheet. The dynamic dropdown in Column B of the Import sheet should be dependant on the value chosen in Column A of the import sheet.

但是要找到相应的系列"，我需要匹配数据表中的 ID.(Eicher ID 应与系列父 ID 匹配；B 列和 D 列)

However to find the corresponding "Series" I need to match the ID's from the data sheet. (Eicher ID should match the Series Parent ID; Column B and D)

下面的截图应该能更好地解释它；

Screenshots down below should explain it better;

我在 User Sheet.A3 中选择了 Eicher，现在我想从 DataSheet Column B (mmcMake-24046283) 中检索 ID.有了这个，我需要找到所有具有相同系列父 ID 的相应系列.所以在这种情况下，我的下拉菜单应该显示；Eicher 系列、2000 系列、3000 系列、300 系列和 400 系列.

I selected Eicher in User Sheet.A3, now I want to retrieve the ID from DataSheet Column B (mmcMake-24046283). With this I need to find all corresponding Series with the same Series Parent ID. So in this case my dropdown should have shown; Series Eicher, Series 2000, Series 3000, Series 300 and Series 400.

推荐答案

好的，这里是插入验证的代码.检查设置变量"部分以确保正确设置每个变量.抱歉，变量名称相当复杂，但空腹很难合成.:D

Ok, here is a code to insert the validation. Check the "setting variables" part to make sure every variable is properly set. Sorry for the quite complex variables names, but empty stomach makes hard to synthesize. :D

Sub SubDynamicDropdownGenerator()

    'Declarations.
    Dim StrDataSheetName As String
    Dim StrImportSheetName As String
    Dim StrImportColumnMake As String
    Dim StrDataColumns As String
    Dim StrDataColumnSeries As String
    Dim StrDataColumnSeriesParentIDEntire As String
    Dim BytDataColumnMakesIDInternalColumn As Byte
    Dim RngCellWithDropDown As Range

    'Setting variables.
    StrDataSheetName = "Data"                       'Insert here the name of the sheet with data
    StrImportSheetName = "Import"                   'Insert here the name of the sheet with the import (where the range with the dynamic drowpdown is)
    StrImportColumnMake = "A"                       'Insert here the letter of the column where labeled Make (according to your first picture it is A)
    StrDataColumns = "A:E"                          'Insert here the letters of the columns where the data are located in the data sheet (i guess they are A:E)
    StrDataColumnSeries = "C"                       'Insert here the letter of the column where the Series are located in the data sheet (i guess is the C column)
    StrDataColumnSeriesParentIDEntire = "E:E"       'Insert here the address of the column where the Series Parent ID are located in the data sheet (i guess is the E column)
    BytDataColumnMakesIDInternalColumn = 2          'Insert here the internal reference of the MakesID in the data sheet for the VLOOKUP functions (since it's in the second column, i set it to 2)
    Set RngCellWithDropDown = Sheets(StrImportSheetName).Range("B3") 'Insert here the cell on witch you are going to apply the validation dropdown.

    'Setting validation.
    With RngCellWithDropDown.Validation
        .Delete
        .Add Type:=xlValidateList, _
             AlertStyle:=xlValidAlertStop, _
             Operator:=xlBetween, _
             Formula1:="=INDIRECT(""" & StrDataSheetName & "!" & StrDataColumnSeries & """&MATCH(VLOOKUP(" & StrImportColumnMake & RngCellWithDropDown.Row & "," & StrDataSheetName & "!" & StrDataColumns & "," & BytDataColumnMakesIDInternalColumn & ",FALSE)," & StrDataSheetName & "!" & StrDataColumnSeriesParentIDEntire & ",0)&"":" & StrDataColumnSeries & """&COUNTIF(" & StrDataSheetName & "!" & StrDataColumnSeriesParentIDEntire & ",VLOOKUP(" & StrImportColumnMake & RngCellWithDropDown.Row & "," & StrDataSheetName & "!" & StrDataColumns & "," & BytDataColumnMakesIDInternalColumn & ",FALSE))+MATCH(VLOOKUP(" & StrImportColumnMake & RngCellWithDropDown.Row & "," & StrDataSheetName & "!" & StrDataColumns & "," & BytDataColumnMakesIDInternalColumn & ",FALSE)," & StrDataSheetName & "!" & StrDataColumnSeriesParentIDEntire & ",0)-1)"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With

End Sub

就像我说的，只要数据保持按系列父 ID 排序，它就应该可以工作.告诉我是否需要在多个单元格上应用它.我可以相应地编辑代码.另外，如果您需要对非常混乱的公式进行任何解释，请说.

Like i've said, it should work as long as the data stay sorted by Series Parent ID. Tell me if you need to appy it on multiple cells. I can edit the code accordingly. Also if you need any explanation on the really messy formula, just say please.

这篇关于来自其他工作表和列的动态下拉列表(偏移/索引?)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！