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:
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):
After choose data validation, select list and in source write the two possible value for number people(1 and 2 separated by semicolon):
To not allow other values, go to error message and write title for the error window appear when the value is not valid:
The final result is the drop down list with the 2 possible values:
When values are invalid gives the error message:
After configure the first drop drow box, you can configure the others:
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):
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:
As you can see, excel as the hability to use different methods, getting at the final the same results.