Google-sheets – Conditional number format (whole number, percentage) based on the value in another cell

conditional formattinggoogle sheetsgoogle-apps-script

I have a custom function provides results based on user input – once of these variables accepts either "Numerical" or "Percentage", which provides the data as standard number, or a percentage of the total.

I've tried looking at conditional formatting to change the number format based on the value of the above variable's cell, but wasn't successful. Is this possible in Google Sheets? Is there some kind of workaround?

Best Answer

Conditional formatting in Google Sheets does not involve setting number formats. To achieve the desired effect, you can use a script running on edit: 

function onEdit(e) {
  if (e.range.getA1Notation() == 'A1') {
    switch (e.value) {
      case 'Whole Number':
        e.range.offset(1,0).setNumberFormat('0'); 
        break;
      case 'Percentage':
        e.range.offset(1,0).setNumberFormat('0%');   // or 0.##% to allow fractional percentages
        break;
      default:
        e.range.offset(1,0).setNumberFormat('0.##'); 
    }
  }
}

This script checks whether cell A1 was edited, and if so, formats the cell below it accordingly. e.range.offset(1,0) means: one row down, zero columns to the right of the edited cell.

Related Topic