Multiple values in a single cell, and validation

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

My worksheet represents a list of entities E1, anb each E1 can have multiple of other entities E2 associated withit. The most straight-forward way to do it would be to have multiple values in cell - something like {e21, e22, e23, e24}. Te problem I have is validation - I want to validate user's input so that e2x can only be one of the E2 entities defined in another worksheet. So far, I was nto able to come up with validation formula that validates an array of values

- Ily
 
My worksheet represents a list of entities E1, anb each E1 can have multiple
of other entities E2 associated withit. The most straight-forward way to do it
would be to have multiple values in cell - something like {e21, e22, e23,
e24}. Te problem I have is validation - I want to validate user's input so
that e2x can only be one of the E2 entities defined in another worksheet. So
far, I was nto able to come up with validation formula that validates an array
of values.
Click to expand...

You'll find it MUCH SIMPLER to work with these sorts of entries if you use
multiple adjacent columns to hold separate, individual entity codes. Parsing
multiple entity codes out of a single cell is difficult (not impossible), and
trying to apply validation to such entries would be even more difficult,
especially if you try to use Data > Validation to do so because that facility
can't tell users which of possibly several entries are invalid.

If you care about making your users' lives easier, never deviate from one entry
per cell - ever.
 
Just put each value in a separate cell, name the range and refer to it under
allow>list,assume you select the range and call it MyRange, then in
data>validation on the other sheet use allow>list and in the list source box
put

=MyRange




--

Regards,

Peo Sjoblom


Ilya Baimetov said:
My worksheet represents a list of entities E1, anb each E1 can have
Click to expand...
multiple of other entities E2 associated withit. The most straight-forward
way to do it would be to have multiple values in cell - something like {e21,
e22, e23, e24}. Te problem I have is validation - I want to validate user's
input so that e2x can only be one of the E2 entities defined in another
worksheet. So far, I was nto able to come up with validation formula that
validates an array of values.
 
Back
Top