Another dimension

Last week I wrote about basics of table creation. We have learned about useful CTRL+L shortcut, which creates table from range of cells. We have seed how calculated columns help to keep consistency in formulas, and how table references make them easy to understand.
It's time to harness tables to do something more useful. Let's give them additional dimension, using data validation.

3. Help on input data validation

Go back to file, we started build last week. Attentive observer hava already spotted, that our table contains at least 2 columns, where it would be good idea to restrict user input. Typically we use for such task command Data > Data Validation.
Most obviously, such column is Tax%. In every country usually there are just few allowed tax rates. For our exercise please assume, that such list is limited to: 23%, 8%, 5% and 0% (of course you can use values, which are used in your country; for simplicity reasons I will skip any special rates, like "exempt").

• In order to keep clarity in main input sheet, please create new sheet in current workbook.
  
• Give new sheet name "Dimensions" (double clicking on sheet name is fastest way to rename). We will put here all necessary dimensions, we might want to use in later steps, also to validate data.
  
• Starting from cell A1, please fill values as below in single column of cells:

  Rate
  23%
  8%

  
• Now click in cell A1 and press CTRL+L. Verify if range is proper and confirm by clicking OK button
• Rename our new table to "tblTaxRates"
  
• Go back to the first sheet with invoices table.
• To select whole column in table, you don't need to manually select cells inside. Every table column has hot-point, which you can use to make selection. Move pointer close to top border of header of column [Tax%]. Cursor will change into black arrow pointing down:
  
• Click once in this place. All cells inside our column became selected.
• Switch to Data tab on the ribbon and select command Data tools > Data Validation > Data Validation
• [Data Validation] dialog window will appear on screen. In Validation criteria section please select field Allow = List
• In Source field enter reference to our rates column

  =tblTaxRates[Rate]

  and try to confirm with [OK] button

Oops... something wrong? Error "There's a problem with this formula. (blah, blah, blah...)".

So what's wrong? You could even see that proper area is inserted into dialog window. Sooo... Let's think a bit. One of important things you got to remember about validation source, is what type of values are accepted here. Lists as source of validation can be specified in 2 different ways. First one is to enter list elements directly, separated by coma, without equals sign in beginning of text. For example tax rates list:

23%,8%,5%,0%

Important note: Character, which separates list elements, may vary between countries. It's depending on system regional settings. When you are working in english locale, typically list separator is "," (coma). In polish version we use ";" (semicolon) for this purpose. You can check it for example trying to create such list in Data validation and observing results of using coma or semicolon. If dropdown lists in cells do not have multiple elements, you should try to experiment with other sign. Default list separator is also visible in Regional settings in Control Panel. You need to look for a window of Date, time & numbers format, and go to Additional settings inside. Among the others, you will see there default list separator.

Second option is to specify range of cells, which will serve as list of allowed values. Range can be specified directly or via named range. So if we want to use column from our other table, we need to assign named range for this column or convert table reference into direct reference. Let's try this second possibility. We will do the conversion by using Excel function INDIRECT. As a parameter it takes string with reference to be transformed into direct one.

• Replace formula in Source field into below one:

  =INDIRECT("tblTaxRates[Rate]")

  and press OK button
• From now on, the only allowed to be entered values in Tax% column, are those from tblTaxRates. Check it by activating any cell, where we set up validation (column F in first sheet). When list validation is active - dropdown button appears next to the right cell border. Drop it down to see rates from our dictionary: 23% and 8%
  
• So now we will try to enter more rates into helper tale. Go to the sheet Dimensions and add 2 more values of tax to table: 5% and 0%
• As you will write, table will expand itself automatically:
  
• Now return to Invoices table and check what are elements available for user choices.
  

The best left for the end: As you will add new invoices to table, data validation will cover also those new entries. Only requirement is to set it once for entire table column. I will leave checking it up to you: enter few more invoices to our table and look how tax column is validated for inserted records.

For the curious: Usage of INDIRECT function might require from you bit of caution. Especially when your model is still under development and changes frequently. You will have to remember, that every change in table or column name, used as a source for validation, will require updating name provided in INDIRECT function. This can be ommited, if you will use named range with reference to table column. Such named ranges are updated on the fly, when table/column name is updated. To create named range, you can use Name manager available on Formulas ribbon (shortcut CTRL+F3). After assigning named range, you can start using it directly in Data Validation (without need for using INDIRECT).

In next episode we will talk a bit of tables in connection with VLOOKUP. We will create vendors table and try to merge informations with main invoices table. See you soon!