Conditional Formatting, VLOOKUP confusion. Help Please

**hwoodh** · 09-21-2012, 04:09 PM

I've got a file I am using as a status reporting tool. I got the file from a friend and have edited to fit my needs. The first tab, MasterList, pulls data from another tab that is populated with an extract from SharePoint. The SharePoint list has some fields that allow users to enter numbers for Status (1 = Green, 2 and 3 = Yellow, 4 and 5 = Red)

So, what is supposed to happen is, my MasterList tab pulls the numbers from the SharePoint extract tab via a VLOOKUP. The Master is also set up to conditionally format those number into red, yellow green icon sets.

It works perfectly on my friends document. Just the numbers are showing up on my document. Although, if I hard code a 1, 2, 3 etc into one of these fields then it gives me my icon. The weird things is, some of my fields on the SP Extract list were not populated and have 0's in them. When the zero is pulled over to the Master I get my green icon (b/c it's less than 2, which is how the rule reads)

We've tried everything. Deleting the rules and starting over, deleting the entire section and rebuilding the VLOOKUP formulas and formatting rules. Changing the number formatting from text, to numbers, etc.

So, confusing. The thing that really throws me off is that it is working for the 0's.

I hope that makes sense. Will be glad to provide more info if needed.

**zumbalj** · 09-21-2012, 06:40 PM

Can you upload the document with fictionalized data? I have several vague theories that I could put into better trains of thought if I could look at the sheet myself to try to troubleshoot

**protonLeah** · 09-21-2012, 07:01 PM

When you are in Advanced reply, click on the paperclip to open the upload window.

View Pic

**FDibbins** · 09-21-2012, 07:33 PM

have you checked to make surethat the "values" you are pulling into master sheet are in fact values, and not text?

test 1 using =isnumber(cell), if its a value, you will get TRUE. if its false, there are a number of ways to convert them to values.

if you highlight the that range, you should see a green? pointer on the top left of the very 1st cell you highlighted. click on that and it will give you the option to convert the text to values.

or, in a helper column, just add 0 to each cell, or multiply it by 1. you can then either use the values inthe helper column for you master sheet, of copy and "paste values" over your original data and delete the helper column.

hope this helps?

**hwoodh** · 09-24-2012, 05:33 PM

Thanks for the advice. I thought it had something to do with the value, but all the formatting in the world would not work. Then, I thought maybe it's some way that SP was formatting the numbers and it was. I had the SP columns set up as a choice (1-5). When I set them up as numbers it all worked.