How to use a different cell format per country report generated from one templat

[gjl]

Hi,


Just discovered that you can use styles in Excel as well. I have a report (Excel 2003)where I control the number by simply dividing this by 1000, mln or bln (Investments). I think using the appropriate format would be better, but I generate some 20 country reports from the same file. Any suggestion on how to automatically select the correct formatting per country, preferably no VBA because in due time I'll hand the template over to someone else.


GJ

 

[Hui]

Gert-Jan

Setup a range of various format templates in some cells


for Normal, Thousands, Millions and Billions

A1 ###.#

A2 ###,.#

A3 ###,,.#

A4 ###,,,.#

Modify the above to suit your needs


Lets Say the number you want to display is in C1


I would use somthing like


C2 =if(C1<1000,1,if(C1<1E6,2,if(C1<1E9,3,4)))

C3 =text(C1, choose(C2,A1,A2,A3,A4))


You can countryise it via


for Australia, US, Europe, UK

A1 A$ ###.#

A2 $ ###.#

A3 E$ ###.#

A4 & ###.#

Put a Pound sign in where the & is in A4

and change the equation in C2 to suit

 

[gjl]

Hui,


Thanks, I tried it and it works perfect. Didn't know that there was a function called choose. using the text function to control the formatting is really nice too.


Why I choose to divide the number by 1000 or so is that this way you get the result (by vlookup or different) and apply formatting directly to that cell. This makes it easier to control the overall look. The report currently consists of some 8 sheets and will double or treble in time (but that's a whole different discussion).

Less formula's also mean that others that use the template better understand what happens.


Also, in our offices we're using both European and US set ups. In Europe the point is used for thousands, whereas the comma is used for decimals.


Thanks for your solution. Rather ingenious.