Google-sheets – Is it possible to calculate in the background of a data validation cell

google sheetsgoogle-apps-scriptworksheet-function

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:

Sample

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.

Best Answer

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.

Example

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 = {
    'A':1,
    'B':2,
    'C':3
  };
  e.range.setValue(dictionary[e.value]);
}

Reference

Related Topic