Is it possible to calculate in the background of a data validation cell?

I am creating a quite comprehensive spreadsheet that shall help me calculate dice pools in an RPG, but to not trouble you with the details, here is my sample scenario:

In google spreadsheets you can include a custom formula for data validation, which made me wonder if this could be used to calculate inside of the cell that you make a selection from.

So let's say we have Column A which lists various names. Then we have Column B which list corresponding values to the data in A. In Column C there are various modifiers to the values in Column B.

My question is, can I create a data validation drop down list with the entries of column A, but once selected, the data shown is not the text from Column A, but instead the corresponding SUM of B and C?

For reference, here's an explanatory screenshot:


I can imagine something like this might be doable with scripts. However, I would prefer using google's spreadsheet formulas as the data validation popup provides the option to use a custom formula for the range provided. If that's impossible, some hints or leads regarding scripts work as well.

1 Answer 1


You could use a script created using Google Apps Script and on edit trigger to replace the selected value from the drop-down list by the desired value.


Let say that we have

A1 = A
A2 = B
A3 = C

C1 has a data validation to use A1:A3 as the list of valid values. Once a value is selected on the dropdownlist on C1, the following script automatically will replace A by 1, B by 2 and C by 3.

function onEdit(e) {
  if( e.range.getA1Notation() != 'C1') return;
  /* Object that holds as key the values of A1:A3 */
  var dictionary = {


  • Yes that could work. Do you think it is possible to use the custom formula of Google spreadsheets though? I think I am coming to the conclusion said custom formula is only for getting a more dynamic range for the data validation.
    – HackXIt
    Oct 6, 2018 at 14:06
  • @HackXIt Please edit your question to share your attempts to make that work. Oct 6, 2018 at 14:25
  • Using Google Apps script and getting used to that is going to take me a while. I can't use custom formula from Google Sheets because it wouldn't give me a drop down like it does when given a cell range. So I'm going to write something with G-Apps-Script that gets me the results I want, but yea... learning curve.
    – HackXIt
    Oct 9, 2018 at 14:12
  • @HackXIt I added an example. Oct 9, 2018 at 15:10
  • That was a surprisingly easy fix.
    – HackXIt
    Oct 9, 2018 at 15:18

Your Answer

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

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