[Calc] drop down with one option

[LibreGuy]

Hello,

I would like to know if what I want is possible without the use of a macro.

Let’s say we have a Calc file that consists of 2 sheets. In cell A1 of the second sheet I type the value ‘blue’.

Now I wonder if it possible to create a column COLOR in the first sheet in which I can type any color, but where it is also possible to select the value ‘blue’ (from the second sheet) by using a ‘drop down menu’ (arrow).

So for example in the first sheet in cell A2 I type ‘red’, in cell A3 I type ‘green’ and in cell A4 I use the drop down menu and select ‘blue’ (‘blue’ has to be the only value in the drop down menu).

Is this scenario possible?

[LKeithJordan]

Update 1:
Per your request, I am updating my earlier response – but again, I’m away from my desk, so…

When you set up a cell for data validation, you can specify a list for selection, or you can specify a range for the list. The latter option provides you more flexibility and is preferred unless there is a valid reason to go with the former option.

I would suggest playing with the options and validation criteria until you get what you want, but here is one idea. Set a list range that includes your “blue” cell and one blank cell. Then set up a restriction criteria that will deny a null or blank entry. That should allow “blue” and any other non-blank entry (I believe).

=========

Try using data validation. I am away from my desk or I could supply more details, but you should be able to find it.

Please click the check mark next to the response you believe best answers your question.

[LibreGuy]

Thanks for your fast reply. I don’t know how to set it up only for that one value (from A1 on the second sheet) while it still has to accept all other (manually typed) values as well (these other values should not appear in the drop down menu). Hope you can give some more explanation.

[LibreGuy]

I tried your solution but couldn’t get it to work

[LKeithJordan]

Unfortunately, I have no way to work with this while away from my desk. Keep tinkering with the approach. I’m pretty sure it (or something similar) should work. I’ll look further when I am able.

[LibreGuy]

I already tried some things based on your previous answer, but so far no luck. Hope you (or someone else) can give it a try. It’s not hard to get that one option in the drop down menu. But the problem is it won’t accept any other values. Maybe is what I want not possible? :-s

[LKeithJordan]

I was just about to tell you that your mistake is in setting the criteria. I tried to explain that earlier, but without access to my copy of LO Calc, my ability to describe in detail is limited. As I said, I was ABOUT to tell you this, when I saw the post by @PYS. I didn’t get a chance to do more than scan it, but you should follow his instructions closely. I’ll bet you will succeed in your goal. I’ll try to check on you later when I’m able. Good luck.

[PYS]

Hi

Use Data▸Validity:

• Criteria tab:
• Allow:▸Cell Range▸Source: select a cell in in which you have entered “blue”.
• Check Allow empty cells.
• Error Alert tab: uncheck Show error message when invalid values are entered

See ValidityNoMessage.ods (validity in A1, source in E1)

Regards

[LibreGuy]

Thanks, works perfectly! I tried exactly the same thing last night (based on the answer of LKeithJordan) but then it didn’t work. Probably I made a mistake somewhere. Works fine now. Thanks to the both of you!