Simple Macros Table Count Link
 

Hi,

I'm having some problems with a Developer table an need help. I've created a drop down menu from a list (attached). The only this is, once I select the appropriate selection from the menu, I also want to generate what # that selection is from the list (i.e. "Delta" is #2, "Foxtrot" is 8). I have to somehow link the # it is in the list to the output of the drop down menu.Can anyone help me?

Thanks!

Answer: Simple Macros Table Count Link
 

Sure, I can help you with that! Here's how you can link the selection from the drop down menu to the corresponding number in the list:

1. First, make sure that the list is in a separate range of cells from where you have created the drop down menu. For example, if your drop down menu is in cell A1, make sure that the list is in cells B1:B10.
2. Next, select the cell where you want to display the number corresponding to the selection from the drop down menu. Let's say you want to display the number in cell C1.
3. Go to the "Developer" tab in the ribbon and click on "Visual Basic" to open the Visual Basic Editor.
4. In the Visual Basic Editor, click on "Insert" and then select "Module" to create a new module.
5. In the new module, paste the following code:
   
   
   Formula:

   ---

   Function GetNumber(selection As String) As Integer
    Dim listRange As Range
    Set listRange = Range("B1:B10")
    GetNumber = Application.WorksheetFunction.Match(selection, listRange, 0)
End Function 


   ---

6. Close the Visual Basic Editor and go back to your worksheet.
7. In cell C1, enter the following formula:
   
   
   Formula:

   ---

   =GetNumber(A1) 


   ---

   This formula will call the "GetNumber" function that you just created in the Visual Basic Editor and pass it the value of the selection from the drop down menu in cell A1.
8. Now, when you select an option from the drop down menu, the corresponding number from the list will be displayed in cell C1.