Data Validation from Range OR any number in Google Sheets

0
votes

I'm creating data validation in Google Sheets that I would like to be able to accept either:

  1. a selection from a range of cells or
  2. any dollar amount from $0.01-$100.00.

I can see how to do either of these separately in separate cells using the "List from Range" or "Number" options but I don't see how to do both in the same cell.

1
validationgoogle-sheets
I don't think that's possible. Do you really man any number? like from negative infinity to infinity? otherwise, it might be possible to just "append" the list of possible numbers to the end of the range in question using a simple SEQUENCE() formula - MattKing
Good point, the number is a dollar amount from 0-100, I edited the question. Thanks! - Verbivore
Adding 10k cells to accommodate the SEQUENCE() function would work, though it seems like a bit of a kluge. I was hoping for something that would stay within a function. Thanks for the idea, I may use it if necessary. - Verbivore
Have you considered doing this in Apps Script? - Jescanellas
@Verbivore Does the answer provided do what you were looking for? - kirkg13

1 Answers

1
votes

I think I have a data validation formula that combines those two sets of criteria.

=or(and(D1>0,D1<=100),iferror(Match(D1,A2:A5,0)>0),0)

Enter the desired extra values, eg. 250, 999, in cells A2:A5. Then apply the data correction formula to a cell, such as D1.

Here is a samle sheet. https://docs.google.com/spreadsheets/d/16cC7YKYLiG76Rn4tFSIraD1RILwsT0GQyymPat1WM-g/edit?usp=sharing

Let me know if this doesn't achieve the result you want, and I'll try again!