ShogunPatch
Board Regular
- Joined
- May 8, 2009
- Messages
- 52
Are there any built-in functions which allow me to define a custom number format as a parameter? Failing that, is there any way of creating a user defined function that contains a "format_number" parameter in the same way that the TEXT function has a "format_text" parameter?
I have a large spreadsheet calculating many thousands of values. The values in each row need to be displayed in any one of half a dozen or more custom number formats, determined according to a code in column AB. Ideally, the format itself would be defined in a table on another sheet which could be easily amended or added to.
I have tried using conditional formatting, which sort of works, but the problem is that the number format does not always update when I change the code in any given row. This appear to be a known problem, which I have researched on various forums, but none of the mooted solutions have solved it in my case. Perhaps more importantly, if I need to alter or add a custom format, updating all the conditional format rules is a major enterprise.
I have successfully been able to convert the number into a text value which displays in the format I want using the built-in TEXT function. I can then convert the text value back into a number using the built-in VALUE function. The problem is that apart from this seeming to me to be quite a convoluted approach, it also has the unacceptable side-effect of rounding the value, which I don't want.
Any suggestions would be much appreciated.
I have a large spreadsheet calculating many thousands of values. The values in each row need to be displayed in any one of half a dozen or more custom number formats, determined according to a code in column AB. Ideally, the format itself would be defined in a table on another sheet which could be easily amended or added to.
I have tried using conditional formatting, which sort of works, but the problem is that the number format does not always update when I change the code in any given row. This appear to be a known problem, which I have researched on various forums, but none of the mooted solutions have solved it in my case. Perhaps more importantly, if I need to alter or add a custom format, updating all the conditional format rules is a major enterprise.
I have successfully been able to convert the number into a text value which displays in the format I want using the built-in TEXT function. I can then convert the text value back into a number using the built-in VALUE function. The problem is that apart from this seeming to me to be quite a convoluted approach, it also has the unacceptable side-effect of rounding the value, which I don't want.
Any suggestions would be much appreciated.
