3

Regarding Google Sheets -- is there any way to have cell-ranges in data validation depend on the cell itself. For instance, I want G7 to accept input only from E7 and F7. Similarly, I want G8 to accept input only from E8 and F8. But if I copy and paste the data validation from G7 to G8, G8 will incorrectly accept input from E7 and F7 rather than E8 and F8. Sure, I can just do G8 manually, but if I want to be able to do this a thousand times, it's not exactly feasible!

One potential solution that occurred to me would be to include the ROW function in the data validation, but I think I would need to use a custom formula for that and I can't come up with a custom formula that outputs a range. I'd appreciate any advice!

BTW - the motivation for this is to create a Google Sheet implementation of a March Madness bracket where the prices (or payoffs) are endogenous, i.e. determined by people's selections. The Google sheet has a lot of neat features to do it, particularly because everybody can access it online and I can give people editing privileges to just their row of entries. I'll link the bracket here if/when I ever get it up and running!

UPDATE - it turns out you can do this easily in Excel as you can use the $ sign in the data validation just as you can in a formula so that when you copy and paste it updates anything that isn't fixed by $. Google Sheets ignores a $ sign if you put it into the data validation range because and just fixes it either way.

Improve this question
3
  • You might write a script to automate the validation rule creation developers.google.com/apps-script/reference/spreadsheet/…
    – Zig Mandel
    Mar 17, 2015 at 15:39
  • @ZigMandel So I would create it under Tools/Script Editor? I'm not familiar with JavaScript, but could probably stumble my way into a solution. My main problem though is that I would need my function to output a range of cells (rather than a value), and I'd also like the sheet to recognize it as a range and give a drop down list to select from that range. Is that seem possible, or am I on a fool's errand?
    – Shane
    Mar 17, 2015 at 16:08
  • It would be great if I could just specify a range as A'ROW()':B'ROW()'. There's no magical syntax to call functions like that inside a range, is there?
    – Shane
    Mar 17, 2015 at 16:27

1 Answer 1

Reset to default
2

A Custom formula is as a validation rule of:

 =or(G1=offset(G1,0,-2),G1=offset(G1,0,-1))

applied to the whole of ColumnG should work on the 'row by row' basis I think you require.

OR
OFFSET

Improve this answer

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.