totn Excel

MS Excel 2003: Use two combo boxes to retrieve values from a table

Question: In Microsoft Excel 2003/XP/2000/97, my workbook has two worksheets. In the first one there is a table with the financial indicators and their values for three years. So, the table has four columns and five rows. In the second worksheet I'm trying to create two combo boxes and one text box that will show me the value from the table when I pick from these two combo boxes the wanted indicator and year.

Answer: Let's look at an example.

Download Excel spreadsheet (as demonstrated below)

Microsoft Excel

On Sheet1, we have a table with financial indicators for each year.

Microsoft Excel

On Sheet2, we've created two combo boxes. The first one lists the years from Sheet1 and the second combo box lists the financial indicators available for each year.

Microsoft Excel

Next, the user will select a year and indicator value in the combo boxes and the corresponding value from Sheet1 will display in cell B5 using the following formula that uses the ISERROR, VLOOKUP, INT and MATCH functions:

=IF(ISERROR(VLOOKUP(INT(B1),Sheet1!$A$1:$D$6, MATCH(B3,Sheet1!$A$1:$D$1,0),FALSE)),"", VLOOKUP(INT(B1),Sheet1!$A$1:$D$6,MATCH(B3,Sheet1!$A$1:$D$1,0),FALSE))

Now, it's important to note that the combo boxes are populated using VBA code on the Workbook open event as follows:

Private Sub Workbook_Open()

   'Populate ComboBox1 on Sheet1 with year values
   Sheet2.ComboBox1.AddItem Range("Sheet1!A2").Value
   Sheet2.ComboBox1.AddItem Range("Sheet1!A3").Value
   Sheet2.ComboBox1.AddItem Range("Sheet1!A4").Value
   Sheet2.ComboBox1.AddItem Range("Sheet1!A5").Value
   Sheet2.ComboBox1.AddItem Range("Sheet1!A6").Value

   'Populate ComboBox2 on Sheet1 with the indicator values
   Sheet2.ComboBox2.AddItem Range("Sheet1!B1").Value
   Sheet2.ComboBox2.AddItem Range("Sheet1!C1").Value
   Sheet2.ComboBox2.AddItem Range("Sheet1!D1").Value

End Sub
Share on: