Friday again Excellers…Formula Friday time. Today let’s look at how to save time entering data into workbook, by using both a VLOOKUP formula and a data validation drop down list.
I want to select area Managers from a drop down list, and auto populate their staff in another 3 cells in my work sheet which I have set out below.
There are two parts to this process.
- Set up my drop down list in Column D for my Area Managers
- Set up a VLOOKUP to automatically enter the Local Managers based on the Area Manager cell contents
Lets get started.
- Setting Up The Drop Down List.
Our list is going to be based on a the cell range on our work sheet (I3:I7)
- Select the range of cells you want to use the Data Validation drop down box (D4:D6 in this example)
- Data Tab – Data Tools – Data Validation
- Settings Tab – Validation Criteria – List
- The Source of our data list will be the contents of cells I4:I7
- Use the selector to select the cells – Hit Ok to Finish
- We now have the option to select the Regional manager from our drop down list. That is the first part of the process completed.
- Setting Up The VLOOKUP formula to auto populate the local managers.
The Regional Managers always have assigned Local managers, so if we know the Area Manager we can link it to the Local Managers and use a simple VLOOKUP to populate the Local Manager names as we select the Area Managers. Let’s get this job done. Here is our data set up.
- Start typing the formula in cell E4, the first cell we want to populate with the first Local Manager and type the formula
=VLOOKUP($D4, $I$3:$L$7, COLUMNS($I:J), FALSE)
This formula will LOOKUP the value in $D4 (our Area Manager) in the data range $I$3:$L$7, returning the value in COLUMNS($I:J). We want to return an exact value so we enter False.
By making $D4 absolute we can lock in the Column D, but allow the formula to be dragged down the columns, and by locking and making our data range absolute,$I$3:$L$7 we can also easily drag the formula without moving the lookup data range.
By using the COLUMNS function and locking in the first column $I:J we want to return the value from, as we move the formula along the increment is always 1 when you copy the formula along our columns.
So we are all set up to easily drag the formula across all 3 of our lookup columns and down all of our rows without having to write 3 seperate VLOOKUP formulas. Good Times!!!!
- Create A Timestamp In Excel
- Hide all of your Excel formulas from prying eyes!
- Undertand and debug Excel formula errors
- How To Display a Plus + Sign On Positive Numbers With Custom Formatting In Excel
