Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formatting - sort of
I am using VLOOKUP to find a "time" result from a "height" input, and (in
another condition) the reverse of this. The input and output cells work in both circumstances, however I must change the number formatting each time to reflect wether I am inputting (or returning) a time ("00:00") or a regular integer (0.00). Is there a function I can use whereby I do not need to change this formatting...or the formatting changes itself relative to the same condition as the formula? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formatting - sort of
hi
conditional formating can't change number formating. it is restricted to back color, font color, borders, and the font itself. it has no ability to change number formating ie date, time, currency, decimals ect. your best bet is to have the results returned in two different cells so that each could be formated different. otherwize you are looking at a macro and i'm not sure that would work either since a change in formula does not constitute a worksheet change event. maybe the calculation event. Regards FSt1 "kafter244" wrote: I am using VLOOKUP to find a "time" result from a "height" input, and (in another condition) the reverse of this. The input and output cells work in both circumstances, however I must change the number formatting each time to reflect wether I am inputting (or returning) a time ("00:00") or a regular integer (0.00). Is there a function I can use whereby I do not need to change this formatting...or the formatting changes itself relative to the same condition as the formula? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formatting - sort of
It depends on the values you have. A time to Excel is a fraction of a day
(ie, 24 hours = 1). So if, for example, your times are always less than 12 hours, and your heights are always greater than 0.5, you can take advantage of this by using a custom format like: [<0.5]h:mm;#,##0.00 This will display all your times as h:mm, and all your heights as 0.00 Regards, Fred "kafter244" wrote in message ... I am using VLOOKUP to find a "time" result from a "height" input, and (in another condition) the reverse of this. The input and output cells work in both circumstances, however I must change the number formatting each time to reflect wether I am inputting (or returning) a time ("00:00") or a regular integer (0.00). Is there a function I can use whereby I do not need to change this formatting...or the formatting changes itself relative to the same condition as the formula? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formatting - sort of
Hi
It depends on which version of excel you are using. Excel 2003 does not support "number formatting" in conditional formatting Excel 2007 supports this feature (as mentioned he http://msdn.microsoft.com/en-us/libr...ffice.11).aspx ) If you are using Excel 2003 maybe this will help you: http://www.dailydoseofexcel.com/arch...number-format/ Thanks, Victor On Jan 17, 9:18*pm, "Fred Smith" wrote: It depends on the values you have. A time to Excel is a fraction of a day (ie, 24 hours = 1). So if, for example, your times are always less than 12 hours, and your heights are always greater than 0.5, you can take advantage of this by using a custom format like: [<0.5]h:mm;#,##0.00 This will display all your times as h:mm, and all your heights as 0.00 Regards, Fred "kafter244" wrote in message ... I am using VLOOKUP to find a "time" result from a "height" input, and (in another condition) the reverse of this. The input and output cells work in both circumstances, however I must change the number formatting each time to reflect wether I am inputting (or returning) a time ("00:00") or a regular integer (0.00). Is there a function I can use whereby I do not need to change this formatting...or the formatting changes itself relative to the same condition as the formula? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formatting - sort of
Wow, thanks a lot for the advice guys, I shall have a read through all the
suggestions now. Cheers :-) "Victor Lobo" wrote: Hi It depends on which version of excel you are using. Excel 2003 does not support "number formatting" in conditional formatting Excel 2007 supports this feature (as mentioned he http://msdn.microsoft.com/en-us/libr...ffice.11).aspx ) If you are using Excel 2003 maybe this will help you: http://www.dailydoseofexcel.com/arch...number-format/ Thanks, Victor On Jan 17, 9:18 pm, "Fred Smith" wrote: It depends on the values you have. A time to Excel is a fraction of a day (ie, 24 hours = 1). So if, for example, your times are always less than 12 hours, and your heights are always greater than 0.5, you can take advantage of this by using a custom format like: [<0.5]h:mm;#,##0.00 This will display all your times as h:mm, and all your heights as 0.00 Regards, Fred "kafter244" wrote in message ... I am using VLOOKUP to find a "time" result from a "height" input, and (in another condition) the reverse of this. The input and output cells work in both circumstances, however I must change the number formatting each time to reflect wether I am inputting (or returning) a time ("00:00") or a regular integer (0.00). Is there a function I can use whereby I do not need to change this formatting...or the formatting changes itself relative to the same condition as the formula? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Protect Cell Formatting including Conditional Formatting | Excel Discussion (Misc queries) | |||
conditional Formatting based on cell formatting | Excel Worksheet Functions | |||
when i sort, how can i get the formatting to sort along with text | Excel Worksheet Functions | |||
Conditional Formatting that will display conditional data | Excel Worksheet Functions | |||
Excel Sort function should not sort the cell formatting! | Excel Worksheet Functions |