0

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.

Improve this question
6
  • 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 Commented Apr 29, 2020 at 17:22
  • Good point, the number is a dollar amount from 0-100, I edited the question. Thanks! Commented Apr 29, 2020 at 18:36
  • 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. Commented Apr 29, 2020 at 22:55
  • Have you considered doing this in Apps Script? Commented Apr 30, 2020 at 7:25
  • @Verbivore Does the answer provided do what you were looking for? Commented May 2, 2020 at 1:42

1 Answer 1

Reset to default
1

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!

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Hey Kirk, this works great! ...and it doesn't create a drop-down list. I'm guessing that there is not a way to put a formula into a list, and that it wouldn't work to have a range of numbers that large in a list anyway. I'm going with your solution. Thank you!
Yes, the drop down list needs to be a reasonable length of values. For $0.01 to $100 is 10,000 values, so not possible as a drop down. You could consider doing the data validation only for the other amounts, with a drop down list, and allow the user to also type in a value from $.01 to $100, then use conditional formatting to flaghighlight the cell if it didn't meet either the data vailidation, or 0 to $100. If that is possible for your purpose.
@Verbivore Glad I've helped. I put an example of what I meant above in cell D7 in the sheet. But that might not be good if you can't run any risk of a wrong amount being entered.

Your Answer

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.