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,
=IF(RIGHT(A1,1)="s",LEFT(A1,LEN(A1)-1),A1)
or as array formula with CRTL+SHIFT+ENTER in this way
=IF(RIGHT(A1:A5,1)="s",LEFT(A1:A5,LEN(A1:A5)-1),A1:A5)
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.