0

I need a dropdown in my sheet with modifier options for a number. They are in the style of "+2/+1" and "+1/+1/+1" where it could be two numbers or three numbers, separated by a forward-slash and each prepended with a plus.

I can create these as options just fine with no issues, however, upon selecting one Google Sheets will throw an error, "The data that you entered in cell _ violates the data validation rules set on this cell." If I change the invalid option from reject to show a warning, it allows it, thinks the value is an equation, displays the result, and then gives me a warning showing that the value is not within a range which is correct since they're not supposed to be equations.

I've tried using the trick of prepending each option with an apostrophe which does fix them when they're displayed but in the dropdown itself, the apostrophe is visible.

This appears to be a shortcoming of Google Sheets. Is there any way around this at all?

Improve this question

1 Answer 1

Reset to default
2

Substitute your + signs with ﹢(unicode 'small plus sign'). The standard plus sign specifies an equation in exactly the same manner as the equals sign does (a practice borrowed from Excel, which itself does so to maintain the standard originally used by the ancient VisiCalc...) and you cannot circumvent this in any way (other than the use of an alternative symbol which looks the same but does not carry the same meaning internally, as done here).

Based on Rubén's excellent suggestion in the comments, I've observed that by adding a zero-width space character in front of the initial plus sign you can overcome the default behaviour of Sheets in interpreting the cell's contents as an equation, in a manner that is entirely transparent to the end-user. However it's a little tricky to actually use the character because of its zero-width (you can't copy it into the clipboard because there's 'nothing' to select...). So to make a set of dropdown options to apply to a cell, do the following:

  1. Define the list of dropdown options in a column of empty cells on the sheet; rather than entering each one directly as a string, generate each one as, for instance =char(8203)&"+1/+1/+1", where char(8203) is the zero-width space which we are concatenating to the front of the string.
  2. Drag-select all the cells, then go to Insert/Drop-down. This automatically populates the dropdown item list with the contents of the cells you selected
  3. Apply the drop-down to the appropriate cells using 'Apply to range'.
Improve this answer
5
  • That would work, however, I'm not a fan of the additional spacing around the character. The full-width version has the same problem but does at least look better. I suppose I'll have to use this and hope someone has another solution.
    – Spedwards
    Oct 16 at 13:36
  • The only other option I can think of if the spacing is an issue is to use a unicode superscript or subscript plus (⁺ or ₊) which solves the spacing issue although you may then find the size to be a problem.
    – The God of Biscuits
    Oct 16 at 14:46
  • 2
    Another option is to prepend a white space character instead of the apostrophe that Google Sheets cannot automatically trim. n space, m space, non-breaking space, tab, etc. I haven't tried this. Something like this might have some side effects that might or might not be relevant to each user context, so be careful (test first using a disposable spreadsheet; the test should at least include two accounts, spreadsheet owner and one editor, before trying something like this on an important spreadsheet)
    – Rubén ♦
    Oct 16 at 20:14
  • 2
    @Rubén: that's a very good idea - I've just tried this with a unicode zero-width space (char 8203) in front of the strings in the question and this appears to work perfectly...
    – The God of Biscuits
    Oct 17 at 14:00
  • 1
    Thanks for taking the time to think about this, testing and sharing. Please feel free to update your answer to make it visible. I believe that the OP and others will appreciate instructions on how to add the zero-width space.
    – Rubén ♦
    Oct 17 at 15:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.