Validated List

This is a technique I've used in a number of projects. This example is just by way of an illustration and not necessarily of practical use in itself!

In cell D3 we are going to create a string based on the values in cells C1 and E1, with the constraints that:

  • the second value must be equal to or greater than the first value
  • if the first value is changed to be greater than the second value then the user must be informed of that fact.

Example

This example uses interface options:

  • Data Validation
  • Conditional Formatting

and the functions:

  • OFFSET
  • MATCH
  • COUNTA

The source range consists of six values in a range called XXX.

The result of our validation is in cell D3, which is generated from the values in cells C1 and E1. The current result is a legal format.

The value in C1 is selected from a dropdown created using Data > Validation.

This is created from the main menu:

  • Select Data and then Validation.
  • Select the Settings tab.
  • In the Allow dropdown select List.
  • In the Source box enter "=XXX", which is a reference to the range name XXX.
  • To finish, click on OK.
  • (The other tabs, Input Message and Error Alert are not used in this example but are worth exploring when you have a moment).

The value, in E1, is also created using Data > Validation, but notice that the list, to fall in with our constraints, is restricted to the letters B and above.

This is created from the main menu:

  • Select Data and then Validation.
  • Select the Settings tab.
  • In the Allow dropdown select List.
  • This is the clever bit: in the Source box we will enter the formula:

=OFFSET(XXX,MATCH(C1,XXX)-1,,COUNTA(XXX)+1-MATCH(C1,XXX))

  • The OFFSET function can consist of up to five arguments, some of which don't have to be used. You can see that, in our example, the third and fifth arguments are not used.
  • The first argument, OFFSET(XXX,MATCH(C1,XXX)-1,,COUNTA(XXX)+1-MATCH(C1,XXX)), is a reference to our source range.
  • The second argument, OFFSET(XXX,MATCH(C1,XXX)-1,,COUNTA(XXX)+1-MATCH(C1,XXX)) tells us which row, from the range XXX, from which we want start our reference.
  • The third argument is empty: OFFSET(XXX,MATCH(C1,XXX)-1,,COUNTA(XXX)+1-MATCH(C1,XXX))
  • The fourth argument, OFFSET(XXX,MATCH(C1,XXX)-1,,COUNTA(XXX)+1-MATCH(C1,XXX)), tells us how many rows we want to use from the remainder of the range. 

The final part of the example turns cell E1 red when its value becomes illegal after the value in cell C1 is changed.

This is created from the main menu:

  • Select Format and then Conditional Formatting.
  • In the first box, select the dropdown and choose Formula Is.
  • This presents one other option box where we create the test using the formula =$C$1>$E$1.
  • Now click on Format and choose a colour (here we've used red) from the dialog box.
  • To finish, click on OK.

We have put the result in cell D3 which concatenates the results of cells C1 and E1 and puts a : between them.

See also:

Published: 28-May-2005
Last edited: 01-Mar-2011 20:50