0

Selection only by NL(Lookup)

Jet Reports Historic Posts

February 23, 2015 15:22

None

Follow

Hi all

I'm using NL(Lookup) to select from a specified list of values when setting the report filters.

Is it possible to lock manual input? Only the values from the lookup-list should be selected.


thanks in advance

• Facebook
• Twitter
• LinkedIn

6 comments

• 0
  
  Jet Reports Historic Posts February 23, 2015 16:01

  Hi Haraldtba,
  
  I've attached an example of what I think you are asking for. You will create your own "table" of values to lookup.

  • custom-lookup.xlsx
• 0
  
  Jet Reports Historic Posts February 23, 2015 18:33

  Instead of preventing manual input or forcing a user to use the lookup function, Jet provides an alternative: validation of the values entered/selected in the Value column of the Options page.
  
  What you need is:
  - an additional column with "Valid" as keyword;
  - a function to check wether the content of a cell is valid or not - the result must be True or False.
  
  I've attached an example.
  
  Two things to be aware of:
  - if you leave the cell in the Valid column blank, then there is no check: all entries are valid;
  - you may use a Jet function (e.g. like NL(Count) or NL(First)) for your validation.

  • Custom Lookup with Validation.xlsx
• 0
  
  Jet Reports Historic Posts February 24, 2015 07:56

  @Hans: Thanks, that's cool! :)
  
  Nearly perfect. Is it also possible to exclude "*" on validation?
  
  Only values from my list of values should be valid.
  
  thanks

• 0
  
  Jet Reports Historic Posts February 24, 2015 10:54

  You're welcome.
  
  Sometimes you need to be a bit inventive with Excel / Jet functions. Maybe an additional IF will do? Or an IF with NL(Count) in which you specify the max number of values to be selected?

• 0
  
  Jet Reports Historic Posts February 24, 2015 18:57

  Haraldtba -
  
  Your IF statement could be something like: CODE(reference cell)=42
  42 is numeric code equivalent to the asterisks.
  
  Or, if any other answer would have to be more than a single character long, you could try something like: LEN(reference cell)=1
  The LEN command looks to see how many characters are in the reference cell, and an asterisks will return a one.

• 0
  
  Jet Reports Historic Posts February 25, 2015 11:15

  thanks all, mission accomplished!! :)