Optimization using data validation tool in excel

Subscribe my youtube channel!

 

In the previous post you learn how to use Vlookup using 2 lookup tables.

In this post, using the same data, i want to make otimization of the data table search (blue table) , that returns the room price in function oh Hotel name, Number of person and Season:

image2

To this 3 input parameters (Hotel name, Number of person and Season) we used editable cells (A11,B11,C11) where we could write any value/text.

In this post i’m gonna transform this 3 cells in 3 drop down lists with the purpose of just allowing the possible value (cell A11= 1;2 / cell B11= low;high and cell C11= Hotel Summer;Hotel Madrid; Hotel Village) and reject other values. To make this change i use data validation tool (list):

excel_data_validation

After choose data validation, select list and in source write the two possible value for number people(1 and 2 separated by semicolon):

data_val_list

number_people_list

To not allow other values, go to error message and write title for the error window appear when the value is not valid:

number_people__error.png

The final result is the drop down list with the 2 possible values:

number_people_list_1

When values are invalid gives the error message:

number_people_list_2

After configure the first drop drow box, you can configure the others:

data_validation_season

data_validation_hotel

Now you have all the 3 input cells configured with drop drow lists, giving a professional aspect to the table.

Now the final change. If you remember you use a IF function to choose what table to choose (low or high season):

image6

The logical_test used in the IF function in the previous post was:

ISNUMBER(SEARCH(B11;A1))  -> see if text B11 appears in text A1

A way to simplify this test is seeing the lenght of cell B11 (if lenght of B11 is equal to 3, we have “low” in cell B11; if is not 3 the cell has the value “high”):

LEN(B11)=3 is the logical test, and the final result is:

IF(LEN(B11)=3;VLOOKUP(C11;B2:D5;MATCH(A11;B2:D2;0);FALSE);VLOOKUP(C11;I2:K5;MATCH(A11;I2:K2;0);FALSE)).

As you can see, excel as the hability to use different methods, getting at the final the same results.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s