I have a budget spreadsheet which contains tables that have 3 columns- Expected Income, Actual Income and Difference between the two. The last row of the table is is a total row. I need these tables to be used in £, $ and €.

I currently have a currency dropdown list set up where you can choose from the three currencies. This works perfectly in the Difference column where the following formula is set up – =IF(E9=””,””, $F$40 & TEXT(E9-D9, “0.00”)). This formula is set in the Difference column so that it is empty until data is entered into actual the Actual column but also so that when data is entered it shows as the currency selected from the dropdown list.

My issue is that I am struggling to get the correct formula for the Expected, Actual and Total columns. I need the Expected and Actual Columns to show as empty until data is entered, but when data is entered I need it to show in the currency selected from the dropdown list.

The total columns currently are autosum, I need these to also show as the currency selected from the dropdown list.

Please see table for reference.

(https://i.sstatic.net/pzFZmNfg.png)

I have tried conditional formatting for this but it doesn’t seem to work.