software

I have produced a custom number format that will format positive numbers in
blue, negative numbers in red, it enters zero values as 'n/a' and text in
green.

However, I have applied this number format to a worksheet which contains a
vlookup. One of the cells in my lookup has returned #REF so obviously my
formula is wrong somewhere - this is fine, I can work that one out.

What I cannot understand - the text #REF doesn't appear in any of the
colours I specified in the custom number, so what has it been created as??
It can't be a number because it would appear in blue and if it was classed as
zero, it would appear in green?? The rest of my vlookup has appeared in the
format I specified.

I'm baffled. Can anybody shed any light??

Thank you.

Louise

The reason it is showing up as #REF is because there is no corresponding
value in your vlookup table.
Thre reason it is not formatting as a color is because #REF is not a number.

I would suggest you look at the value you are looking up and see if it is in
the lookup table. That might help...

Anna

quot;Louisequot; wrote:

gt; I have produced a custom number format that will format positive numbers in
gt; blue, negative numbers in red, it enters zero values as 'n/a' and text in
gt; green.
gt;
gt; However, I have applied this number format to a worksheet which contains a
gt; vlookup. One of the cells in my lookup has returned #REF so obviously my
gt; formula is wrong somewhere - this is fine, I can work that one out.
gt;
gt; What I cannot understand - the text #REF doesn't appear in any of the
gt; colours I specified in the custom number, so what has it been created as??
gt; It can't be a number because it would appear in blue and if it was classed as
gt; zero, it would appear in green?? The rest of my vlookup has appeared in the
gt; format I specified.
gt;
gt; I'm baffled. Can anybody shed any light??
gt;
gt; Thank you.
gt;
gt; Louise

I have worked out why I have got the Error message, I'm ok with that.
My query is - if it isn't formatted as a number, what is it formatted as?
It isn't text either because if it was, I have asked for text to be printed
in a different colour also, and it isn't, it's still the default black??

Thanks.
Louise

quot;The Meccaquot; wrote:

gt; The reason it is showing up as #REF is because there is no corresponding
gt; value in your vlookup table.
gt; Thre reason it is not formatting as a color is because #REF is not a number.
gt;
gt; I would suggest you look at the value you are looking up and see if it is in
gt; the lookup table. That might help...
gt;
gt; Anna
gt;
gt; quot;Louisequot; wrote:
gt;
gt; gt; I have produced a custom number format that will format positive numbers in
gt; gt; blue, negative numbers in red, it enters zero values as 'n/a' and text in
gt; gt; green.
gt; gt;
gt; gt; However, I have applied this number format to a worksheet which contains a
gt; gt; vlookup. One of the cells in my lookup has returned #REF so obviously my
gt; gt; formula is wrong somewhere - this is fine, I can work that one out.
gt; gt;
gt; gt; What I cannot understand - the text #REF doesn't appear in any of the
gt; gt; colours I specified in the custom number, so what has it been created as??
gt; gt; It can't be a number because it would appear in blue and if it was classed as
gt; gt; zero, it would appear in green?? The rest of my vlookup has appeared in the
gt; gt; format I specified.
gt; gt;
gt; gt; I'm baffled. Can anybody shed any light??
gt; gt;
gt; gt; Thank you.
gt; gt;
gt; gt; Louise

an error code isn't formatted, i would assume. It won't be able to recognize
anytype of conditional formatting. If you want it to show up as a number or a
text, change your formula to =IF(ISERROR(your_function)=TRUE,quot;your_new_valuequot;)
This way if you get an error message- it will change to a number or a text
that you can format. This quot;iserrorquot; function will recognize #REF and #N/A- if
you want them formatted different- you can use =if(ISERR(
- this will recognize any error value except #N/A
I hope this answers your problem

Anna

quot;Louisequot; wrote:

gt; I have worked out why I have got the Error message, I'm ok with that.
gt; My query is - if it isn't formatted as a number, what is it formatted as?
gt; It isn't text either because if it was, I have asked for text to be printed
gt; in a different colour also, and it isn't, it's still the default black??
gt;
gt; Thanks.
gt; Louise
gt;
gt; quot;The Meccaquot; wrote:
gt;
gt; gt; The reason it is showing up as #REF is because there is no corresponding
gt; gt; value in your vlookup table.
gt; gt; Thre reason it is not formatting as a color is because #REF is not a number.
gt; gt;
gt; gt; I would suggest you look at the value you are looking up and see if it is in
gt; gt; the lookup table. That might help...
gt; gt;
gt; gt; Anna
gt; gt;
gt; gt; quot;Louisequot; wrote:
gt; gt;
gt; gt; gt; I have produced a custom number format that will format positive numbers in
gt; gt; gt; blue, negative numbers in red, it enters zero values as 'n/a' and text in
gt; gt; gt; green.
gt; gt; gt;
gt; gt; gt; However, I have applied this number format to a worksheet which contains a
gt; gt; gt; vlookup. One of the cells in my lookup has returned #REF so obviously my
gt; gt; gt; formula is wrong somewhere - this is fine, I can work that one out.
gt; gt; gt;
gt; gt; gt; What I cannot understand - the text #REF doesn't appear in any of the
gt; gt; gt; colours I specified in the custom number, so what has it been created as??
gt; gt; gt; It can't be a number because it would appear in blue and if it was classed as
gt; gt; gt; zero, it would appear in green?? The rest of my vlookup has appeared in the
gt; gt; gt; format I specified.
gt; gt; gt;
gt; gt; gt; I'm baffled. Can anybody shed any light??
gt; gt; gt;
gt; gt; gt; Thank you.
gt; gt; gt;
gt; gt; gt; Louise

That's great, thank you for all your help.

Louise

quot;The Meccaquot; wrote:

gt; an error code isn't formatted, i would assume. It won't be able to recognize
gt; anytype of conditional formatting. If you want it to show up as a number or a
gt; text, change your formula to =IF(ISERROR(your_function)=TRUE,quot;your_new_valuequot;)
gt; This way if you get an error message- it will change to a number or a text
gt; that you can format. This quot;iserrorquot; function will recognize #REF and #N/A- if
gt; you want them formatted different- you can use =if(ISERR(
gt; - this will recognize any error value except #N/A
gt; I hope this answers your problem
gt;
gt; Anna
gt;
gt; quot;Louisequot; wrote:
gt;
gt; gt; I have worked out why I have got the Error message, I'm ok with that.
gt; gt; My query is - if it isn't formatted as a number, what is it formatted as?
gt; gt; It isn't text either because if it was, I have asked for text to be printed
gt; gt; in a different colour also, and it isn't, it's still the default black??
gt; gt;
gt; gt; Thanks.
gt; gt; Louise
gt; gt;
gt; gt; quot;The Meccaquot; wrote:
gt; gt;
gt; gt; gt; The reason it is showing up as #REF is because there is no corresponding
gt; gt; gt; value in your vlookup table.
gt; gt; gt; Thre reason it is not formatting as a color is because #REF is not a number.
gt; gt; gt;
gt; gt; gt; I would suggest you look at the value you are looking up and see if it is in
gt; gt; gt; the lookup table. That might help...
gt; gt; gt;
gt; gt; gt; Anna
gt; gt; gt;
gt; gt; gt; quot;Louisequot; wrote:
gt; gt; gt;
gt; gt; gt; gt; I have produced a custom number format that will format positive numbers in
gt; gt; gt; gt; blue, negative numbers in red, it enters zero values as 'n/a' and text in
gt; gt; gt; gt; green.
gt; gt; gt; gt;
gt; gt; gt; gt; However, I have applied this number format to a worksheet which contains a
gt; gt; gt; gt; vlookup. One of the cells in my lookup has returned #REF so obviously my
gt; gt; gt; gt; formula is wrong somewhere - this is fine, I can work that one out.
gt; gt; gt; gt;
gt; gt; gt; gt; What I cannot understand - the text #REF doesn't appear in any of the
gt; gt; gt; gt; colours I specified in the custom number, so what has it been created as??
gt; gt; gt; gt; It can't be a number because it would appear in blue and if it was classed as
gt; gt; gt; gt; zero, it would appear in green?? The rest of my vlookup has appeared in the
gt; gt; gt; gt; format I specified.
gt; gt; gt; gt;
gt; gt; gt; gt; I'm baffled. Can anybody shed any light??
gt; gt; gt; gt;
gt; gt; gt; gt; Thank you.
gt; gt; gt; gt;
gt; gt; gt; gt; Louise