Conditional formatting - sort of

[kafter244]

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?

 

[FSt1]

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

 

[Fred Smith]

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

 

[Victor Lobo]

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 here:
http://msdn.microsoft.com/en-us/library/bb286672(office.11).aspx )

If you are using Excel 2003 maybe this will help you:
http://www.dailydoseofexcel.com/archives/2004/06/21/conditionally-formatting-number-format/

Thanks,
Victor

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 than12
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

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?

 

[kafter244]

Wow, thanks a lot for the advice guys, I shall have a read through all the
suggestions now.

Cheers

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 here:
http://msdn.microsoft.com/en-us/library/bb286672(office.11).aspx )

If you are using Excel 2003 maybe this will help you:
http://www.dailydoseofexcel.com/archives/2004/06/21/conditionally-formatting-number-format/

Thanks,
Victor

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

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?