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

"Louise" wrote:

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

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

"The Mecca" wrote:

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

"Louise" wrote:

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

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,"your_new_value")
This way if you get an error message- it will change to a number or a text
that you can format. This "iserror" 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

"Louise" wrote:

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

"The Mecca" wrote:

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

"Louise" wrote:

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

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

Louise

"The Mecca" wrote:

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,"your_new_value")
This way if you get an error message- it will change to a number or a text
that you can format. This "iserror" 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

"Louise" wrote:

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

"The Mecca" wrote:

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

"Louise" wrote:

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