Free Microsoft Excel 2013 Quick Reference

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?

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?

- Conditional Formatting W/Icon Sets in Analysis Services Pivot Table
- Adding to TODAY() in a conditional format rule
- Conditional Formatting Icon Sets
- Cell references in conditional formatting formula
- Conditional Formatting With R1C1 References
- Cannot use ISEVEN or ISODD functions in Conditional Formatting
- Changing a Formula in Conditional Formatting
- WEEKDAY in Conditional Formatting?
- VLOOKUP in Conditional Formatting
- Cannot use ISEVEN or ISODD functions in Conditional Formatting
- Help with formating cells off another cells Conditional Formatting>Color Scales
- Allow user to define the color in conditional formatting
- Exceeding 3 Criteria Limit In Conditional Formatting
- Excel's 3 Criteria Limit in Conditional Formatting
- Change cell color in Conditional Formatting if not blank
- How to enter more that 3 conditions in conditional formatting?
- Controllng Borders in Conditional Formatting
- Trap #Div/0 in conditional formatting
- In conditional formating, is it possible to do?
- Can I add More than three condition in conditional formating
- Confused about relative references in named formulas
- Help Using Formulas in Conditional Formatting
- Multiple FIND functions in Conditional Formatting
- More condition in Conditional Formatting

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?

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!

"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?

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.

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

I tried the above and I keep getting an error.

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

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

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

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.

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

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

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.

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 SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

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:Any assistance would be much appreciated.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 SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Thank you

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

Thank you!

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.

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

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!!

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 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?

the previous column =Y. Do I use "formula is" in Conditional Formatting, and

what then would the formula be?

=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

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