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

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.

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

• 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. Oct 6, 2018 at 14:06