Another way.
I removed GBP format from Debit, Credit, and Total columns, as it did feel wrong to show e.g. AUD values formatted as GBP. And I added a calculated currency column to Data sheet - it was possible to write the formula for conversion rate so it read the currency directly from Branches sheet, but in this way the formula is easier (and it will be useful to see the local currency in Data table too).
With this design, you haven't change the design of Data and Rates worksheets, when any new branches/currencies are added in future. And you haven't to redesign your Rates sheet and rate calculation formula on Data sheet every month too!
Another thing to consider - change the format for Month columns from "mmm yyyy" to "yyyy mmm" (e.g. "2022 Apr"). Then it will be possible to sort your tables by month, when you need this (and months data validation list will work better too). As you use text values for months, this means you have to use a temporary columns in all tables with Month column to convert current month names into new format, and then overwrite the old values with new ones.
|