Free Microsoft Excel 2013 Quick Reference

Relative References in Conditional Formatting (bars & scales)

I am an Equipment Operator with the Navy Seabees, and the Yard Boss at Camp Moreell in Kuwait. We have CESE (Civil Engineering Support Equipment) going into IEM (Inactive Equipment Management). What this means; our CESE has to be cycled (checked for preventative maintenence issues, started up, ran...) once a week if it hasn't been used within a seven day period, but CESE going into IEM only needs to be cycled once per month.

Column E in my report reflects that as a two part eight day graph, with the formula =TODAY()-8 for the shortest end of the bar graph and the red of the graded color scale, and =TODAY() for the long end of the bar and the green of the color scale. If a piece is cycled today I enter today's date and the bar shows full and the scale is green. As that date approaches 8 days past, the bar moves to the left and the scale changes yellow to red.

I need that graph (column E) to change to a thirty day scale when I change the LOCATION (column D) to IEM, back to an eight day if I change it to DISPATCH, and to clear out completely if I change to any other location.

I've tried a few different things, but can't seem to get any of them to work. One thing I tried that I thought would work--but didn't--is to make column H (which I planned to hide) equal eight if the corresponding row in column D equals DISPATCH (using an IF formula), and to equal thirty if it's IEM. Then I wrote my graph formula a few different ways to the general effect of =TODAY()-H:H but no way that I wrote it worked. I couldn't figure out a way to just make DISPATCH equal a hidden value of eight, and IEM to equal thirty, but since the formula I did for the method I tried didn't work anyway, I wouldn't have gotten that to work either. I was able to get column H to reflect eight or thirty in accordance with column D, but I can't get the graph to cooperate.

I also tried entering the formula =IF(D:D="IEM",TODAY()-31,TODAY()-8) into the Conditional Formatting formula field for the color scale and data bar, but I get the error "You cannot use relative references in Conditional Formatting criteria for color scales, data bars, and icon sets."

What's my work around?


I'm using Excel 2007 and want to use Conditional Formatting --> Icon Set on an Analysis Services Pivot Table I have created. This is to trend student GPA's:

Col A Col B Col C Col D
Stud A 2.944 3.056 3.000
Stud B 1.250 1.600 1.444

Col B is Term 1, Col C is Term 2, and Col D is Cumulative GPA. I'm trying to use the conditional formatting on Col D, I select the column, goto Conditional Formatting --> Icon Sets and choose 3 arrows. It creates the formatting. Next, I go back to Conditional Formatting --> Manage Rules, and Edit the Icon Set I just created. I'm choosing Format all cells based on their values; and this is where the trouble starts. At the bottom, I've tried to choose a Type of Number and/or Formula, and when I try to select Column C as what to compare Column D against, it fusses at me and says:

You cannot use a direct reference to a worksheet range in a Conditional Formatting formula. Change the reference to a single cell, or use the reference with a worksheet function, such as =SUM(A1:E5).

OK, fine, I change it to reference the cell containing the first "real" value in Column C and click Apply, but then each row wants to reference THAT cell and not the cell on it's specific row. I go back into the Edit, see that it is set to look like $C$7, remove the $$ so that it is C7 and try to save it, and get this message:

You cannot use relative references in Conditional Formatting criteria for color scales, data bars, and icon sets.

So obviously I'm missing something, can anybody tell me what?

Hi,
please forgive any mistakes as I am not massively proficient in this but...

I am trying to use a conditional format 3-colour scale to colour dates for when things such as insurance policies are due for renewal. I would like any cells with a date of within 2 months of today to be red, 2-3 months to be orange or whatever, and more than 3 months to be green.

I have tried to set the rule using formulas but cannot get it to work - it accepts the formulas but no colours appear.

The three formulas I used were:

1. =TODAY() - this worked fine when the other 2 were set to percentages

2. =DATE(YEAR(DATEVALUE("TODAY()")),MONTH(DATEVALUE("TODAY()"))+2,DAY(DATEVALUE("TODAY()")))

3. =DATE(YEAR(DATEVALUE("TODAY()")),MONTH(DATEVALUE("TODAY()"))+3,DAY(DATEVALUE("TODAY()")))

The only other alternitive I tried was setting cells to have those values and trying to get the formula to use them, but excel told me you cannot "use relative references in conditional formatting for colour scales".

Is it just not possible to do this? or are my formulas not doing what I think they are?

any help would be much appreciated!

I'd like to use Icon Sets (3 arrows) to show a trend (increase, decrease, same) from one month to another for various records of data. I set the conditional formatting rules for one cell, but when I tried to change my reference from absolute to relative to copy it to the rest of my range, I get an error message that says:

"You cannot use relative references in Conditional Formatting criteria for color scales, data bars, and icon sets."

I do not want to have to set this up separately for over a hundred cells. Is there any way around this problem?

Hi

I am a bit stuck. I have entered a conditional format so that the cell (H5) will turn red if it is below 75% of the value of another cell (F5). It turns amber if it is betwwen 75% and 100% of value of the cell (F5), and green if it is over 100% of the cell value (F5).

This all works fine. However I would like to copy this conditional formatting down the column from H5 to H300. When I attempt to do so it keeps the the same cell references in the formulas within the conditional formatting - therefore refering to cell F5.

I would like the formula to automatically change the same cell reference to the F column on that same row. E.g. If the formatting is in cell H15 I want the formula to refer to F15.

Is is possible for this to happen automatically as to do these changes by hand will take forever.

Cheers.

Simon.

Does anyone know if it's possible to use R1C1 style references in conditional formatting formulas?

Eg., =R[-1]C > 1

I tried the above and I keep getting an error.

Attempting to use the ISEVEN() or ISODD() functions in Excel 2003 SP2 for
Windows or Excel 2004 for Mac OS X within a Conditional Formatting formula
produces the error:

"You may not use references to other worksheets or workbooks for Conditional
Formatting criteria."

Even though the formula works fine in a cell, the Conditional Formatting
glitch happens even with a new Excel file using a generic test formula such
as: =iseven(2)

These functions would be useful in Conditional Formatting to produce
alternating row formats (shading) for entire columns efficiently instead of
applying a defined style, which for entire columns will tend to greatly
degrade file performance.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...et.f unctions

I have a formula in conditional formatting and I want to edit the formula.
However, when I click in the formula and try to move around in the formula
using the arrow keys, cell references and $ appear in the formula. How do I
edit a formula that I have placed in conditional formatting. I am using Excel
2003

Thanks very much for your help.

Regards,

Dee

Excel2003 ... My Cols are A thru H =

Date - Mon - Tue - Wed - Thu - Fri - Sat - Sun

Cell A2 = a date (or is blank (empty)) ... format = mm/dd/yy
Cells B2 thru H2 contain Conditional Formatting as follows:

Cell B2 ... =if(weekday(A2,2)=1
Cell C2 ... =if(weekday(A2,2)=2
Cell D2 ... =if(weekday(A2,2)=3
Cell E2 ... =if(weekday(A2,2)=4
Cell F2 ... =if(weekday(A2,2)=5
Cell G2 ... =if(weekday(A2,2)=6 ... this one is a problem???
Cell H2 ... =if(weekday(A2,2)=7

All above are working ok, except ... =if(weekday(A2,2)=6 ... this formula in
Conditional Formatting is setting off the Conditional formatting when Col A
reference cell is BLANK (empty) ... Anyone know what gives?

Note: if I place "=6" in any of the other Cells it also sets off the
Conditional formatting when Col A reference cell is BLANK (empty)???

Thanks ... Kha

Hi All

I have a formula I want to use in conditional formatting, which is:

=IF(VLOOKUP(B7,$C$38:$D$99,2,0)<>I7,1,0)

I want to be able to then drag this formula down so that it applies to the rest of the row. Values would then be red if the result is true. The problem I have is that although B7 and I7 are not absolute references, they do not update as you drag down the formula.

So in row 8, the formula still reads:

=IF(VLOOKUP(B7,$C$38:$D$99,2,0)<>I7,1,0)

when i would like it to read:

=IF(VLOOKUP(B8,$C$38:$D$99,2,0)<>I8,1,0)

Can anyone help me get around this? I have tried using define name for the vlookup formula but that still treats the cell references as absolute.

Thanks everyone!

Brokovich.

Attempting to use the ISEVEN() or ISODD() functions in Excel 2003 SP2 for
Windows or Excel 2004 for Mac OS X within a Conditional Formatting formula
produces the error:

"You may not use references to other worksheets or workbooks for Conditional
Formatting criteria."

Even though the formula works fine in a cell, the Conditional Formatting
glitch happens even with a new Excel file using a generic test formula such
as: =iseven(2)

These functions would be useful in Conditional Formatting to produce
alternating row formats (shading) for entire columns efficiently instead of
applying a defined style, which for entire columns will tend to greatly
degrade file performance.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...heet.functions

Hello to you & thank you for your time.

First, I’d like to send a sincere heartfelt THANKYOU to members Leith Ross and To royUK for all the help they have offered in getting my membership on the forums activated.

I am currently working on a EXCEL OFFICE 2007 workbook that is very Data and Macro extensive.

On one worksheet I am summarizing some data in cells B60:S79.
All cells are using “Absolute Referencing” to cells in other worksheets.
Cells B60:B79 are names. All other columns in this range are numerical.

This data is being sorted (through Macro buttons) by Column Header in C59 ( Largest to Smallest ) then by Q59 ( Largest to Smallest ) then by B59. ( “Names” A to Z )

Cells C60:79 are using Conditional Formatting > Color Scales. Cells Q60:Q79 use Conditional Formatting > Icon Sets.

So as the data changes in cells C60:79 The Conditional Formatting > Color Scales changes the cells fill colors respectfully.

What I am trying to do, or need to have happen is to have the rest of the cells in that ROW automatically use the same FILL color. So as to more easily track the statistics by name (B60:79) across each row as the data is sorted and updated.

So if cell C62 changes to cell fill color #FFCC22 then cells B62, D62, E62, F62, G62, H62, I62, J62, K62, L62, M62, N62, O62, P62, Q62, R62, S62 all change to cell fill color #FFCC22 respectfully as C62.

Is this possible? I can’t seem to figure this one out.

Thank you for your time,

-Martin

Hi there!!!

I am faily good with excel formulas but a complete novice with macros and VBA.

I am working in excel 2010 and have created a worksheet which color codes timelines i.e. I have put in conditional formatting on a range of cells where 14 criteria are evaluated and 1 color is defined for each criteria.

In this sheet the color for each criteria has been defined by me however I would like to give user the option for choosing the color for each criteria.

The code segment from "Get Around Excels 3 Criteria Limit in Conditional Formatting" works perfectly if I type the variable directly into the cell, but not in a range of cells.

That is to say, if I copy the source cell to a range of cells I receive a "Run-time error 13: Type mismatch" error".

Is there a way to get around this?

This is the code segment I've tried in the worksheet.


	VB:
	
 Range) 
    Dim icolor As Integer 
     
    If Not Intersect(Target, Range("b2:q53")) Is Nothing Then 
        Select Case Target 
        Case "IBBCH", "ibbch" 
            icolor = 36 'light yellow
        Case "OBBCH", "obbch" 
            icolor = 34 'light turqoise
        Case "OBBRDG", "obbrdg" 
            icolor = 35 'light green
        Case "LNCH", "lnch" 
            icolor = 53 'brown
        Case "BRK", "brk" 
            icolor = 15 'gray-25%
        Case "AV", "av" 
            icolor = 10 'green
        Case "AS", "as", "med", "MED" 
            icolor = 3 'red
        Case Else 
             'Whatever
        End Select 
         
        Target.Interior.ColorIndex = icolor 
    End If 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I am trying to use the Get Around Excel's 3 Criteria Limit in Conditional Formatting pricinple to change the background colour of a series of cell ranges based upon their content.

The code works if the variable is typed directly into the cell, but not when the cell is populated from a formula.

Is there a way to do this?

a sample formula populating the cell is

=IF('Nov 05 Admin'!B65="",VLOOKUP(C$4,'Shift Pattern'!$C$2:$W$533,VLOOKUP('NOVEMBER 05'!$B65,'Shift Pattern'!$Y$1:$Z$20,2,),),'Nov 05 Admin'!B65)

I have tried the following code in the worksheet.


	VB:
	
 Range) 
     
    If Application.Intersect(Target, Range("C8:AG12,C16:AG20,C24:AG28,C32:AG36,C40:AG48,C56:AG65")) Is Nothing Then 
         
        Exit Sub 
    Else 
        Select Case Target.Value 
             
        Case Is = "˝H am" 
            Target.Interior.ColorIndex = 46 
        Case Is = "˝H pm" 
            Target.Interior.ColorIndex = 46 
        Case Is = "D" 
            Target.Interior.ColorIndex = 6 
        Case Is = "E" 
            Target.Interior.ColorIndex = 36 
        Case Is = "H" 
            Target.Interior.ColorIndex = 46 
        Case Is = "L" 
            Target.Interior.ColorIndex = 35 
        Case Is = "N" 
            Target.Interior.ColorIndex = 4 
        Case Is = "O" 
            Target.Interior.ColorIndex = 40 
        Case Is = "OS" 
            Target.Interior.ColorIndex = 7 
        Case Is = "OT" 
            Target.Interior.ColorIndex = 8 
        Case Is = "T" 
            Target.Interior.ColorIndex = 3 
        Case Else 
             'Whatever
        End Select 
         
        Target.Interior.ColorIndex = Target.Interior.ColorIndex 
    End If 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Any assistance would be much appreciated.

Thank you

Can someone please tell me what is the formula to setup in Conditional Formatting for this:

Within only Column A, cell change to color yellow if it is not blank. Everything blank cell leave it unchange.

Thank you!

I need to know how to enter more that 3 conditions in conditional formatting...It looks like it only allows 3. I hope somebody can help. Here is my example...

I have a cell that can equal anywhere from 0-15. I need it to turn yellow if it equals 3, 6, 9, or 12 and turn red if it equals 15. That is 5 conditions. Can somebody help me figure this out? Thanks.

I would like to create a border in conditional formatting using the diagonal lines. How can I do this?

is it possible to detect the #Div/0! text / error in conditional formatting,
so that if it appears, due to empty cells else where in the sheet, I can set
the font colour to the same as the background colour to hide it?
I tried using an 'If' statement to detect empty cells, then not using the
formula if any were empty, but my users need to edit the formula to suit
their needs, and don't have the knowledge to use the 'If' statement.

Any help appreciatted.
Thanks

Neil

Hi and thanks for the previous “helpful” tips you offered, it was really
helpful!

Is it possible at all to do the following?

In conditional formatting, Formula is: IF the cell “A1” is cell shaded with
say color blue (FormatConditions.Interior.ColorIndex = 55), set this cell
“A2” to color red, else FALSE.

So my question is, if conditional format can detect a cell pattern color to
use it for an IF formula?

Thanx so much!!

I am making an attendence sheet, in which I have to put SL(sick leave),
CL(casual leave), PL(marriage leave), ML(Maternity Leave). I wanted to
highlight automatically these leave with different colour by the help of
conditional formating. Is it possible to add condition more than three in
conditional formating.

I'm new to this, so perhaps I'm missing something obvious.

I'm reading the "Excel 2002 Inside Out" book. In the section "Using
Relative References in Named Formulas" in chapter 12, it mentions a
sample formula like "=Sheet1!B22+1.2%". It says that if the active cell
was B21, then the reference to B22 will always translate to the cell
just below the active cell.

What bothers me is that the information about what the active cell was
when the formula was defined is not displayed with the formula. Because
of that, isn't it impossible for someone to tell by looking at a named
formula that uses relative references to know what cell the relative
cell reference refers to? That can't be right, can it?

I am trying to make one column gray based on the whether or not the value in
the previous column =Y. Do I use "formula is" in Conditional Formatting, and
what then would the formula be?

The following formulas work when used in Conditional Formatting:

=AND(FIND(B1,"h"),LEN(G1)=2)
=AND(FIND(B1,"w"),LEN(G1)=3)
=OR(AND(EXACT(B1,"h"),LEN(G1)=3),AND(EXACT(B1,"w") ,LEN(G1)=2))

but this one does not

=OR(AND(FIND(B1,"h"),LEN(G1)=3),AND(FIND(B1,"w"),L EN(G1)=2))

The only difference is the use of FIND instead of EXACT.

RFM

Hi ,
i´m trying to put some alternative in conditional formatting. For example If the number is bigger than 5 and smaller than 10 than do the formatting .
Any idea how to do this ?

Thanks