How do I dynamically format the currency in google sheets?

Current data

Desired Output

Instead of using google apps script and manually format (since I have 1000 rows of data with multiple currency), I'm looking for cell formula approach in google sheets.

By referencing cell in "Currency", the values in "Budget" and Amount Spend" will be formatted accordingly.

CodePudding user response：

If you were using Excel, than the immediate answer would be to use Conditional Formatting. However, as you probably know, Google sheets does not allow conditional formating to be applied to Number Format. This leaves you with limited options. One key thing your post didn't include is that you probably wish to retain the values in the Bugdet and spend column. That makes it impossible to be be accomplished with a formula (happy to be proved incorrect if anyone knows something I don't...).

The only way to get the data to appears as you shows, as by using a formula to concatenate or join the cells. See sample sheet with JoinValues tab.

Better Data Structure

Not to be critical but the real issue you're facing is that your data is not in an optimal a structure. Your reporting requirement is very normal, you just need to unpivot your data. This would mean you would have one column with values/amoun, but you would add a column for ACTUAL or BUDGET (this field is commonly called version or category for tools that present such information like BPC, Essbase, Tagetik). This would essentialy double the number or rows but offer you lots of pivoting options and methods of calculations. See same spreadsheet for what Unpivoted data would look like.

Google Script

While the question did explicitly state WITHOUT Google Scripting, that would appear to be the only way to accomplish dynamic numeric formating by row. For anyone who finds this question and WOULD be open to scripting, this procedure below would accomplish the desired task in this sample sheet.

/** @OnlyCurrentDoc*/

function fixFormat() {
  const currencyRange = "b2:b";// column b
  const valueRange = "c2:d"; //number columns
  const sheetName = "JoinValues";

  const dataSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);

  var valueFormats = dataSheet.getRange(valueRange).getNumberFormats();
  var allCurrencies = dataSheet.getRange(currencyRange).getValues();

  for (var i = 0; i < allCurrencies.length; i  ) {
    var currencyID = allCurrencies[i][0];
    if (currencyID != '') {
      for (var c = 0; c < valueFormats[i].length; c  ) {
        valueFormats[i][c] = '"'   currencyID   '  "'   valueFormats[i][c]
      }
    } else {
      break;
    }
  }
  dataSheet.getRange(valueRange).setNumberFormats(valueFormats);
}