#### 3-Color Scale Vlookup for Current Month/Previous/Pre-Previous

```Is it possible to Conditionally Format a cell so that the colors are
based off of VLOOKUP's?  What I mean is I have a table on another tab
and it has by month how complete the build is.  I want the conditional
format to color the percent of the current month red, percent of
previous month yellow and pre-previous month green.  I tried putting
the formula =("41227-1",BuildData,18,FALSE) as the current, =
("41227-1",BuildData,17,FALSE) as the previous, and =
("41227-1",BuildData,16,FALSE) as the pre-previous.  However, it uses
only the cells value to color and it always colors the scale red.
```
 0
NeoFax
1/5/2010 8:38:22 PM
excel.misc 78881 articles. 5 followers.

2 Replies
689 Views

Similar Articles

[PageSpeed] 8

```On Jan 5, 3:38=A0pm, NeoFax <neofa...@gmail.com> wrote:
> Is it possible to Conditionally Format a cell so that the colors are
> based off of VLOOKUP's? =A0What I mean is I have a table on another tab
> and it has by month how complete the build is. =A0I want the conditional
> format to color the percent of the current month red, percent of
> previous month yellow and pre-previous month green. =A0I tried putting
> the formula =3D("41227-1",BuildData,18,FALSE) as the current, =3D
> ("41227-1",BuildData,17,FALSE) as the previous, and =3D
> ("41227-1",BuildData,16,FALSE) as the pre-previous. =A0However, it uses
> only the cells value to color and it always colors the scale red.

So my second thought was to try and get the three values into the
cell, but that didn't work.  So my question now is can Conditional
Formatting gradient color a single cell based off of three different
values?  i.e. Value A is 50%, Value B is 75%, Value C is 90% and from
0-50 the color is blue and 51-75 purple and 76-90 the color is yellow.
```
 0
NeoFax
1/6/2010 1:29:43 PM
```On Jan 6, 8:29=A0am, NeoFax <neofa...@gmail.com> wrote:
> On Jan 5, 3:38=A0pm, NeoFax <neofa...@gmail.com> wrote:
>
> > Is it possible to Conditionally Format a cell so that the colors are
> > based off of VLOOKUP's? =A0What I mean is I have a table on another tab
> > and it has by month how complete the build is. =A0I want the conditiona=
l
> > format to color the percent of the current month red, percent of
> > previous month yellow and pre-previous month green. =A0I tried putting
> > the formula =3D("41227-1",BuildData,18,FALSE) as the current, =3D
> > ("41227-1",BuildData,17,FALSE) as the previous, and =3D
> > ("41227-1",BuildData,16,FALSE) as the pre-previous. =A0However, it uses
> > only the cells value to color and it always colors the scale red.
>
> So my second thought was to try and get the three values into the
> cell, but that didn't work. =A0So my question now is can Conditional
> Formatting gradient color a single cell based off of three different
> values? =A0i.e. Value A is 50%, Value B is 75%, Value C is 90% and from
> 0-50 the color is blue and 51-75 purple and 76-90 the color is yellow.

I guess this is unachievable.
```
 0
NeoFax
1/8/2010 7:04:59 PM

Similar Artilces:

every time i send a reply or email to sometime how can i tweak outlook 2003 to save the email address to my contacts automatically?? - Thank you Mayur Mayur Patel <patelmb@vt.edu> wrote: > every time i send a reply or email to sometime how can i tweak > outlook 2003 to save the email address to my contacts automatically?? http://www.slipstick.com/contacts/addauto.htm -- Brian Tillman ...

number format #3
I want to change number format to indian number format form american number format for example: the american format is like 200,200,000.00 I want to change it as indian format 2,00,20,000.00 Adding to M ike's response, #","##","##","###.00;-#","##","##","###.00 to use the same format for negative numbers with a preceding minus sign -- HTH Bob Phillips "Mike" <mike22p@hotmail.com> wrote in message news:#gGdQskWDHA.652@TK2MSFTNGP10.phx.gbl... > #","##","##"...

Upgrading callouts and assemblies from 3.0 to 4.0
I have existing callouts and assemblies in 1.1 doct net versions. What would be the steps to upgrade these callouts and assemblies to 3.0? I have made calls to CRM and Metadata webservices from the assemblies. How much and what changes are required to these assemblies for making them useable in MS CRM 4.0? Please give me some ideas.. -- romeo!! For a complete conversion there are a few steps, depending on how you use the webservices this may take some work. 1. Create the new plug-in assembly classes: these classes must implement the IPlugin interface. In the Execute method you must ma...

Considering cells colored via conditional formatting
EXCEL 2007 Any piece of code which, for example, select or count the cells which are colored yellow, amongst the currently selected range, because of meeting ANY criteria of CONDITIONAL FORMATTING. In other words, considering cells which are not actually possessing yellow as an interior color but being displayed so because of CONDITIONAL FORMATTING. -- Thanx in advance, Best Regards, Faraz There are two pages at Chip Pearson's wepage that you need to look at. the 2nd gives a detailed example of how to do what you want. 'Conditional Formatting' (http:/...

-- Geoff Kidd ...

crm 3.0
We put item serial numbers in the Invoice Products and print them on the Invoices. I would like to be able to view and search ALL Invoice Products to find them based on the Serial Numbers. Product Invoice [x] box is disabled in customize entity. How can I change this to enable so that the entities can easily be part of the Services menu? Our system is highly customized so I will accept any ideas or suggestions. ...

Dating 6 events for 5 groups on a 18 month cycle
I have been trying tocalendarise 6 key dates in a cycle for 5 groups Each group A,B,and C all have different start dates to their cycle in the 18 month period - after that time it all repeats itself. Is there anyway this can be done I also need to find some way of labelling these key dates - Thanks no Denise - Despite Colin's certainty that it can't be done, I suspect all it requires is a better statement of your problem. Are you talking about a Gantt chart? If so, check out some of the links on this web page: http://peltiertech.com/Excel/Charts/GanttLinks.html - Jon ------- ...

CRM 3.0 VPC May 2006 Extended Edition
hI am trying to load the VPC Image of CRM 3.0 May 2006 Extended Edition - I download the files - click on the zip file - shows crm.vhd - click on this and then winzip keeps asking for location of file 1 ----- what am I doing wrong or what am I missing? I select a folder and click ok but it keeps asking this -- Thanks in advance for any/all help. ...

How can I color every other row
Help please. I want to make it easier to use a large spreadsheet where two rows are used for each record. Filling in the background color of every second row prevents mistakes when entering data. I want to color only the used range, not the entire row. I recorded a macro and got the following: TheRange.Activate With Selection.Interior .ColorIndex = 36 .Pattern = xlSolid End With I really appreciate the help. On way: Dim iCtr As Long With ActiveSheet.UsedRange For iCtr = .Row To .Rows(.Rows.Count)....

Salesprocess not running after migration from CRM 3.0
Hi, We are trying to perform a test migration from 3.0 to 4.0. The migration goes OK, but after the migration all the sales process workflows have an error. The wfl's have the status "waiting", which is OK, because they are all waiting for an earlier created activity to reach the status "completed". Already found some info on an issue where workflow problems could occur when the website points to an IP adress in IIS, so changed that to "All unassigned". This didn't make any difference. After turning on tracing the tracefile for the AsyncService ther...

&[page]+2 not giving me 3....
Greetings I am using two versions of excel - one is excel 2000 where I us the &[page]+2 to give me page 3 and continue numbering the page bu when I send the document to a colleague using excel 2002 in win-x instead of printing page 3, 4 , 5 it prints page 12, 22, 32, 42, 52 an so on. Does ayone have a solution for this? Iva -- Message posted from http://www.ExcelForum.com In Excel 2002, there is an option on the File->Page Setup screen to start page numbering at 3 instead of Auto. If that option also exists in 2000, then you should be all set. Otherwise; I'm at an impass, t...

Excel color pull down does not have some stanard color
Hi All One of my excel file in Excel 95 , then save to Excel 2003. Today I found that some standard color missing in pull donw color box. Such as Orange. For new create file have 40 standard color. Some color when mouse move up shown "color scheme" Do you know how to restore those missing color ? My Excel file have Module and Class. moonhk Tools>Options>Color>Reset HTH Bob "moonhkt" <moonhkt@gmail.com> wrote in message news:fafe8fd3-6b9e-43f6-b1c0-13f1c17679b9@l12g2000prg.googlegroups.com... > Hi All > > One of my ...

Sending problem #3
For the last few days, I'll be working online, press Send on an e-mail, and it just sits in the Outbox. Opening the e-mail from the Outbox and pressing Send yields the same result. Pressing Send and Receive on the toolbar has become the only way to send e-mail. I still have "Send immediately when connected" selected in my Options. I ran a virus scan with no results. These e-mails don't have large attachments or anything else unusual. Any ideas? Thanks, Ben ...

How to share Outlook 2003 with 3 computers (peer to peer)
How to I share my Outlook folders, calendar, contacts, tasks, notes? NO Exchange Server! ( I am peer to peer with 3 computers in my office) help! David Create a Personal Folders file on a drive/share that all 3 computer have access to. This PST file can be opened via the File > Open > Outlook data file on each machine. Now for the bad news, Outlook opens the file for exclusive access. Therefore when Machine A has Outlook running, Machines B/C cannot open/work with this shared personal folders file. By the way, there are 3rd party solutions that let multiple machines share t...

Send As permission #3
I am trying to grant a user Send As permission. I enable Advanced view, go to the Users object that I want to grant access to, go to the security tab, click advanced, and click Add, but I do not see Send As permission there. I can grant full mailbox access, but the grantee still shows "Sendt on Behalf Of" in the e-mail when they send for that user. What did I miss? Never mind....you have to do it on the domain controller, not from an exchange server.... "Tom Felts" <tfelts@mckennalong.com> wrote in message news:e3MFRAbUFHA.3312@TK2MSFTNGP09.phx.gbl.....

IIS/DNS Requirements for CRM 3.0 SBE?
I read my previous posting and throught rewording might make it easier to understand my question. The SBS 2003 server I am installing CRM onto has 3 IP addresses bound to it. 192.168.200.1-3 I am trying to install CRM using the .3 address and applying a specific host URL to it. For instance the .1 address is server.domain.local, the .2 address is app.domain.local, and I would like to use .3 as crm.domain.local. <A Records> for each host have been entered in DNS. The configuration wizard that appears after the initial install defaults to http://192.168.1.3:80 on the final screen ...

How do I get my signature to show up in the right color?
When I created my signature, I specified the font color as red and that's how it shows up in Tools:Options:Mail Format:Signature. However, when I go to prepare an email, it shows up in black. How do I fix this? What message format is the message using? It will show in HTML and Rich Text, but not in Plain Text. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. All unsolicited mail sent to my personal account will be deleted without reading. After furious head scratching, Rikesha asked: | When I created my signature, I specified the fo...

Can't open attachments #3
On all the e-mails that I receive that show they have an attachment (the paperclip to the left of their addess), when I click on the paperclip, the attachments are not bold and when I attempt to open or save the attachment, nothing works. What do I need to do? Hi, I'm having the same problem. I'm using Outlook 2000, SP3 from MS Exhange 2000. Attachments are blocked. I know this is a Microsoft Security issue, however I beleive there is a registry fix for this. Any help would be much appreciated! Thanks, Marc >-----Original Message----- >Is this Outlook or Outlook Expr...

sending previous "contacts" to new "contacts" folder
I've attempted to send my old contact list to 'Windows mail' but have not been successful at this point. What am I doing wrong? Bonnie What do you mean by "send" your old contacts? Where are the old contacts located, on a different computer? If so, which mail program were you = using on the older computer? --=20 Gary VanderMolen, Microsoft MVP (Mail) http://mvp.support.microsoft.com/default.aspx/profile/vandermolen "Bonnie " <tallhyland@verizon.net> wrote in message = news:O66XmaUbKHA.4708@TK2MSFTNGP02.phx.gbl... > I've att...

Attn: M02,3,4 users who use Passport/WinLiveID
You will get broken at the end of July, 2008. (That's about 45 days from now.) See http://support.microsoft.com/kb/894020. You should remove the Passport/WinLiveID from your file before that time. (File|Password Manager). There have been many indications this day would come. Now you all have a date certain. Does this also means backup files taken from M02,3,4 will be inaccessible, even if they are opened with a current version of Money? Although if you have to go back four, five or six years you probably have bigger issues. But I have at times opened older backup files, even five...

VLOOKUP challenge
Hi I am building a standard report template where the format does not alter. I pull information from another table into this standard report. The trouble is that the report I pull from changes so when I have a formula =VLOOKUP(A674,B11:AW673,11,0) and there is no information I get a #n/a error. I would like to have a 0 or blank returned to enable totalling to work as some cells have figures in them whilst others have n/a which prevents =SUM(F696:F697) etc from working. Cheers Brian =IF(ISERROR(VLOOKUP(A674,B11:AW673,11,0)),0,VLOOKUP(A674,B11:AW673,11,0)) -- Gary''s Student - gs...

Blocked senders list #3
When I add senders to my blocked senders list, the next time I open Outlook, there is nothing in my blocked senders list. I have went into my registry under Current Users/Identities/Outlook Express/Rules and deleted the Rules folder with the understanding that it will be rebuilt. This however hasn't fixed the Blocked senders list. Is there something else I need to do? I'm a novice on my computer. Any help would be welcome! Thanks Kris ...

Junk E-mail Filter #3
Hi there, I am running Exchange 2003 SP2. When I use Outlook 2003, go to tools, then options, then Junk E-Mail, I get a message that says "The Junk E-mail Filter is not available for your Microsoft Exchange Server e-mail account because you are working online. To enable the junk email filter for this account , switch to cached exchange mode." But is this true? It seems not - because I am getting Junk email in my Junk E-Mail folder courtesy of IMF. So what is this message telling me? And therefore, how do I go about getting the white & black lists going for individuals if...

workspace background color
I'm using Publisher 07 on Windows XP system. How can I change the workspace color from dark blue to white. It's hard to find objects I've placed there when the color is so dark. Thanks ...

Deferred Revenue #3
We have a client that needs to enter GL Deferred transactions in different currencies. I know standard GP Deferral Module doesn't allow this. Does anyone know of any 3rd party apps that allows for this type of transaction? Ed ...