Google-sheets – Google Sheets: How to turn an input into a value

formulasgoogle sheets

I am trying to figure out how to turn an input into a value?

For example:

Someone enters N/A in a cell in one column. I want it to appear as a 0 in the next column.

What can be done?

Best Answer

You can try

=INDEX(IF(ISERR(P2:P8-O2:O8),0,P2:P8-O2:O8))

(Please adjust ranges to your needs. If still an issue please share more info.)

Functions used:

Related Solutions

Google-sheets – Change an input value in a cell to another value depending on what the input was

This is more a workaround than a real solution, but add this to the scripteditor and change to sheet name..

function onEdit(e) {
if (e.source.getActiveSheet()
    .getName() !== 'Sheet2' || e.range.columnStart !== 7) return;
e.range.setFormula("=rept(char(9734)," + e.value + ")")
}

Since I do not know how to work with a star symbol (char) in GAS, the script writes a formula to the edited cell (in col G) which replaces the number that is typed in with that number of stars.

Google Sheets – Turn Table into List of Row/Column/Value Tuples

Alternatively, for a 1-formula solution automatically updating when new rows are added, try:

=ArrayFormula({trim(transpose(split(concatenate(rept(A2:A&char(9),counta(B1:1))), char(9)))), transpose(split(rept(join(char(9), B1:D1&char(9)),counta(A2:A)), char(9))), transpose(split(CONCATENATE(B2:D&char(9)), char(9)))})

NOTE: after adding a name in column A, column B, C and D can not be left blank.

Example spreadsheet

Best Answer

Related Solutions

Google-sheets – Change an input value in a cell to another value depending on what the input was

Google Sheets – Turn Table into List of Row/Column/Value Tuples

Related Topic