0

I was usually working on Excel sheets, in which I had option to choose currency (file was used as a template). It was based on Conditional formatting and simple formulas so it worked like charm.
So it look like this. I have a drop down list with couple currencies. I chose one, and the formatting of tables is based on this choice.

Now I have to prepare similar document on Google Sheets but there is where I have problem. Conditional formatting in GSheets is not that sophisticated as in Excel. so my question is:

Is there a simple way to choose currency used in multiple tables? Any way to make formatting depend and on cell value?

2
  • Welcome to Web Applications Stack Exchange. If you still have the Microsoft Excel file, you can convert it into a Google Sheet. Almost all formatting should follow, including conditional formatting for things like conditional cell borders that cannot be set through the user interface in Google Sheets. If you need more help, share a publicly editable sample spreadsheet with realistic-looking data. Show your hand-entered expected results there. Use Insert > Note to describe what you want. Aug 10 at 15:59
  • Yes, i do have it - I was working on it for some time. As for importing to google sheet - it does containt normal conditional formatting (so changing colors based on value etc. ) but it does not contain currency values. The lin for dummy file is here: docs.google.com/spreadsheets/d/… As for my progress, i only found that it might be done in GSheets with use of scritps, i don't see any information how to apply cell format to a cell (is this even possible?) Aug 12 at 14:38
0

You can implement a variable currency using a separate reporting sheet that copies the values from the Tracker sheet with formulas like these:

={ Tracker!B1:5 }

={ Tracker!B6:C26 }

=arrayformula( 
  text( 
    Tracker!D6:E26; 
    regexextract('Delivery Outline'!$C$5; "\((..?)\)") 
    & 
    " #,###,##0.00" 
  ) 
)

The text() function in the latter formula observes the currency selected in the Delivery Outline sheet.

See the new 'Tracker - report' sheet in your sample spreadsheet. The formulas are in the cells that have been marked yellow.

2
  • Hey, Thank You for that update - maybe not a perfect solution, but that is some workaround since I need to change the currency for different reporting. Only problem is, i understand i would need to duplicate all sheets to create that connection between both tables correct? I might just use that formulas to create a Report template, choose currency, and afterwards just save it as 'template' - it might be some timesaving then - any way Thank you very much for help ;) Aug 16 at 10:42
  • Ahh yes, Thank you :) Aug 17 at 21:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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