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.

migrated from superuser.com Oct 5 at 21:12

This question came from our site for computer enthusiasts and power users.

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. – HackXIt Oct 6 at 14:06
  • @HackXIt Please edit your question to share your attempts to make that work. – Rubén Oct 6 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 at 14:12
  • @HackXIt I added an example. – Rubén Oct 9 at 15:10
  • That was a surprisingly easy fix. – HackXIt Oct 9 at 15:18

Your Answer

 

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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