Formula to remove last letter to use it in data validation

[cgkmal]

Hello to all,

I'm trying to set a data validation in B1 from values in A1:A5.

A1: Car
A2: Papers
A3: Pencil
A4: Pants
A5: Shoes

What I want is show in dropdown list the words in singular (without the "s" at the end).

The formula below works if I introduce it in a normal cell,

Please Login or Register  to view this content.

or as array formula with CRTL+SHIFT+ENTER in this way

Please Login or Register  to view this content.

But I get the message that the formula has error when I introduce any of both in "Validation Criteria"-->Allow "List"-->Source.

May somebody help me fixing my formula or with other one that works in data validation.

Thanks in advance.

[Tony Valko]

Are you using that formula as the source?

You need to use the RANGE as the source.

Data Validation
Allow: List
Source: =B1:B5

[cgkmal]

Hello Tony,

Thanks for answer.

Yes, I'm using the formula in source field, since I don't want to use an extra/auxiliary range if it is possible to avoid it.

Regards

[Tony Valko]

If you use a formula it has to resolve to a range of cells, like when you define a dynamic range with a formula. You're using a formula that resolves to a range of cells.

[cgkmal]

When I use the first formula as data validation it works but shows only one word.

Is there a way to conver the resultant array of 2nd formula in something acceptable to use in data validation?

Thanks again for the help

[Tony Valko]

No, can't be done with that formula.

The formula would have to resolve to a range of cells.

[cgkmal]

Ok, thank you for the help Tony.

Regards

[Tony Valko]

You're welcome. Thanks for the feedback!


If your question has been solved please mark the thread as being solved.

In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.