Excel Course: Using a Database (more)

In the "Developer" tab, click on "Insert" and then "Combo Box".

add dropdown list excel billing database2

Select your drop-down list and click on "Format Control".

Input range: select the list of client names from sheet "DB".

Cell link: I2 = the cell that contains the "1".

format dropdown list excel billing database2

The drop-down list will now change the client number each time it is changed.

display dropdown list excel billing database2

[Optional] Hide the client number using the drop-down list.

hide value excel billing database2

You can download the completed exercise if you need it: lesson10-end.xlsx

In this example, you can also use the INDEX function instead of VLOOKUP, the formula to display the name would be: =INDEX(DB!A:E;I2+1;2).

Another example

You can download another example lesson10-index-match.xlsx that is similar to what we have just done, but uses the INDEX-MATCH Functions instead of VLOOKUP as well as drop-down list "data validation".


index match validation excel billing database2

Continue Your Excel Training

Would you like to go further with Excel ? Our VBA course (macros) is just the thing for you !

If you don't know what VBA is about, give this simple application a try. It will give you a good idea of what macros can do:

calendar excel billing database2