Free Microsoft Excel 2013 Quick Reference

Hi Everyone,

I have a spreadsheet I am working on for a prospective retail store. I am currently comparing the potential ROI of Renting vs. Owning.

In the spreadsheet, I have created a drop down list that reads

Mortgage

Rent

I would like to have a custom format in a different cell that conditionally will read:

"Rent:" #.00 "Per Sq. Ft."

or

"Mortgage:" #.00 "Per Sq. Ft."

Can the conditional formatting "Use a Formula to determine which cells to format" make the adjustments (I have already tried several ways to write the formula in conditional formatting with no success).

Any help would be appreciated

I have a spreadsheet I am working on for a prospective retail store. I am currently comparing the potential ROI of Renting vs. Owning.

In the spreadsheet, I have created a drop down list that reads

Mortgage

Rent

I would like to have a custom format in a different cell that conditionally will read:

"Rent:" #.00 "Per Sq. Ft."

or

"Mortgage:" #.00 "Per Sq. Ft."

Can the conditional formatting "Use a Formula to determine which cells to format" make the adjustments (I have already tried several ways to write the formula in conditional formatting with no success).

Any help would be appreciated

- Conditional Formatting, using custom formats
- Can I save conditional formatting rules for use in other workbooks? [Excel 2010]
- Conditional Formatting Two Conditions, So close!!
- Conditional Format - Excel 2007
- HasFormula, Cond. Formatting, and slow spreadsheet
- Use a formula to determine which cells to format?
- Conditional Formatting two statements in formula input
- Excel 2007 Conditional Formatting accepts formula but does not work
- Conditional Formatting - Set value to empty cell
- Formula to determine under which Header Label a specific value is found
- Excel Conditional Formatting Based on another cell
- Conditional Formatting in Excel 2010
- Conditional formatting if one cell holds same data as another
- Using a formula to determine column reference
- Conditional Formatting with AND, ISBLANK, and DATE
- Conditional Formatting With Custom Number Formats
- Can I use a value in a cell, in a formula, to determine what cell is to accessed?
- Applying Colors to Top/Bottom Values in a List in Excel 2007
- Basic Conditional Formatting
- Help with conditional formating
- Using a formula to create named range reference
- Using a formula to calcualte different mileage rates
- Conditional Formatting auto color help
- Using a formula to create named range reference

I have a spreadsheet I am working on for a prospective retail store. I am currently comparing the potential ROI of Renting vs. Owning.

In the spreadsheet, I have created a drop down list that reads

Mortgage

Rent

I would like to have a custom format in a different cell that conditionally will read:

"Mortgage:" #.00 "Per Sq. Ft."

or

"Rent:" #.00 "Per Sq. Ft."

Can the conditional formatting "Use a Formula to determine which cells to format" make the adjustments (I have already tried several ways to write the formula in conditional formatting with no success).

Any help would be appreciated

I use

(1) Home>Conditional Formatting>Use a formula to determine which cells to format;

(2) Type in "=MOD(ROW(),2)=1";

(3) And apply a light gray shade so that it will work on the screen and when I print from my old B&W LaserJet.

Every time I do this I have to search for the correct formula, then go through the steps to create a new rule. I grant that this doesn't take a huge amount of time but it'd be much easier if I could some how save the rule for use in any workbook.

Is it possible to do so?

Thanks for any suggestions.

I was wondering if anyone could help me out with this conditional formatting problem I think I am so close!!! I know how to do it as a traditional "if/and" statement, but I just don't know how to input it into the Format Values where this formula is true:

box.

This is the formula which I have if I was just trying to do it 'normally' instead of conditionally formatting it. In plain English, if the value in cell A1(relative) is greater than the value anchored in A34 and The values in Sheet2 in cell A1(reletive) is greater than the value anchored in A34 (also Sheet2), then highlight the cell.

ie.

=IF(AND(A1>=$A$34,'Sheet2'!A1>='Sheet2'!$A$34),"Highlight","None"))

But how do I format it properly to put into a Conditional Formatting "Use a formula to determine which cells to format" box

Thanks in Advance for any guidance!

I have selected condtional format > use a formula to determine which cells

to format but am not getting consistent results so something must be

incorrect with the formula.

Thanks

Lisa

I wrote the following function in VBA:

Function isFormula(check_cell As Range) As Boolean isFormula = check_cell.HasFormula End FunctionI then use this formula with Conditional Formatting->Use a formula to determine which cells to format-> isFormula(A1)

It works fine for one cell, but if I copy this format into 30 cells my spreadsheet slows down incredibly so that even just moving from cell to cell has a lag.

Is there a way to make this faster? Using Excel 2007...

Thanks

If the number in the cell to the left is smaller, then make this cell GREEN.

What formula would I use under the conditional formatting tool to make the above two scenarios work? Thanks in advance for your help!

I have the following conditional formatting statement that I am having trouble trying to figure out:

I currently have the working statement

I wanted to add that extra statement of logic but am unsure on how to do that.

This formula was added into the "use a formula to determine which cells to format under "format values where this formula is true."

thanks,

-vargs

I'm using the 'Format only cells that contain' rule to apply the CF across a range that defines a given row. That rule lets me place a formula in the cell reference box - but they do not appear to function as they did in 2003.

I also tried the 'Use a formula to determine which cells to format' rule with no better result.

Row 2 has no ConFor, it is supplied as an example of what I'd like to see.

Row 4 shows how Cell Value <> "A" conditionally formats empty cells. The rest of the rows are my efforts to ignore the empty cells.

Row 6 almost works, but row 7 shows how a different 'not between' value set fails.

I want to ignore a designated character, and empty cells.

Am I up against my ignorance - or a bug?

Tried a less detailed post here http://msdn.microsoft.com/en-us/libr...ffice.11).aspx

If I missed an answer to this I am sorry. My searches did not find what I wanted to know.

Is it possible to set a value to an EMPTY cell with conditional formatting?

I am trying this:

On Conditional Formatting:

1) Use a Formula to determine which cells to format

2) =IF(J5=0;"OK";"NotOK") or =IF(ISBLANK(J5);"OK";"Not OK")

Regards,

Elio Fernandes

I want to use a formula to determine what I have on column C, but with a standard formula I can simply drag down without having to change the reference for each section. The different General Ledgers (GL) are moved within headers regularly and I have to be determining under which header each GL belongs to. Note that no one GL will appear in more than 1 section, and note as well that there may be A general section header and then a subsection which is where the GLs will be listed. I want the subsection header (or the header label that is right before the first number is shown).

Is there a formula you guys can think about. I can't think of any...

I'm trying to make a KPI table which would help me to evaluate my employees but i'm stuck on Conditional Formatting.

From the the attach you can see that i have 4 columns (at the end of column is total):

Evaluation 1

Evaluation 2

Evaluation 3

Evaluation 4

Then total in percentage.

What i want to do is that, whenever on total in percentage is on F11 example up 50.00%, than cell B17 should be colored on red (but number 1 on B17 should stay as it is only the cell color should change, and so on colors can be different)

I searched over about this but i found nothing helpful (except use a formula to determine which cells to format, which still doesn't solve my issue)!

Thank you in advance

KPI.xlsx

541.08 541.08 541.08 460.03 460.03 460.03

All of these cells contain formulas, if it makes any difference.

Thanks

I've attempted using a number of formula in the conditional formatting dialogue box (using 'use a formula to determine which cells to format'

TIA

Apologies - dinner bell rang

Can I have a formula in which a cell address (ex: J9) references that letter to determine which column in another worksheet is referenced?

For example, in a formula that reads: ='7765 Act'!R24 can I replace the "R" with with the result shown in the cell where the vlookup is?

The vlookup table looks like this:

April I

August N

December S

February F

January E

July M

June K

March G

May J

November R

October Q

September O

what I want to do is have the table look up the month, for example December, and change the formula to cell S24 instead of the current R24 for November.

Thanks for whatever help you can give, Happy Holidays!

Richard

Here's what I need.

I have a list columns A-M containing data, C and M are date fields. C will always have a date, and M will only have a date when the row has been "Approved."

If the date in C is greater than 12 days away, I would like the entire row to turn yellow.

If the date in C is less than 12 days away AND the corresponding cell in M is blank, I need the entire row to turn red.

I have managed to get the C cell that is greater than 12 days away to turn yellow by using the "Format only cells that contain" rule with Cell Value - Greater Than - =TODAY()+12

However, it only highlights the cell. I tried using the "Use a formula to determine which cells to format" but couldn't get the syntax right.

I've also messed with the ISBLANK to try to get the row to turn red, and had some close calls, but for some reason it would highlight a few rows that actually had data in them.

Thanks for the help!

Greg (broscup)

The conditional formats are each applied to cell C90. I used New Rule > Use a formula to determine which cells to format.

The Equation for the first is:

The Equation for the second is:And so on.

The custom number format corresponding to the first one is:

The custom number format corresponding to the second one is:

And so on.

When I try them out, they don't work the first time you try. When I change the value of E91 from 3 to 4, C90 still has "(#3)".

If I again put 4 into it and hit enter, C90 will change to "(#4)".

OR

If I enter 5 into C90 after having 4 in there, it will then change to "(#4)"

But if I go straight from 3 to 5, it will stay at "(#3)"

I tried assigning different fills to go along with each custom number format. The fills change when they are supposed to.

I also tried assigning pre-defined number formats to the cells and they are also not working correctly

There must be some sort of problem with number formats not updating right away.

Say I have a formula (A1 * B1) + (C1 * B1)

This isn't the actual formula I'm using, but It'll do as an example.

Now instead of just always being B1 in the formula I want this cell to be variable. I would like to have a value in D1, say for example 5. Which will then enter the formula so that instead of accessing B1 it will access B5. Then whenever I change the value of D, say to 25, the B5 cell becomes B25 which will be accessed instead.

The way I tried to express this in formula was (B(D1), thinking that D1 would be replaced with its value which with the B would then become a cell address.

Hope that makes sense, hope more so someone can help me.

I probably should have called it 'Can I use a cell that contains a value, in a formula, to access a different cell'

1. Select a cell in the List and press Ctrl+Shift+* or press Ctrl+A to select the List.

2. Select Home -> Conditional Formatting (in Styles Group) -> Top/Bottom Rules -> Top N Items.

3. In Format cells that rank in the Top select a number, select the formatting from with dropdown list, and click OK.

4. Repeat the steps above to format the Bottom N Numbers.

1. Select a column in the List.

2. Follow the steps above.

To color rows where maximum/minimum values are ,in a specific column:

1. Select a cell in the List and press Ctrl+Shift+* or press Ctrl+A.

2. Select Home -> Conditional Formatting (in Styles Group) -> New Rule.

3. Select Use a Formula to determine which cells to format.

4. In Format values where this formula is true insert the formula =MAX($F:$F) =$F1, click the Format button, select the Fill tab, choose a color and click OK.

5. Add a new rule while changing the formula to calculate the minimum number,

=MIN($F:$F) =$F1.

Be sure to distinguish between Absolute reference and Relative reference when entering the formulas.

Trying format a row's color base upon a simple comparison (if,then) in that row. Example:

=if F10+G10>H10, highlight or fill the entire row red

I'm using 2007 right now. I can't get the conditional formatting tool to do this with a rule "use a formula to determine which cells to format." Not sure what formula to put into the box when creating this rule.

(Also, I am clicking the format button and choosing a fill of red, but what cells or rows is it setting this rule for (what cells or rows will be filled red))?

Ideas? Thank you so very much!

I want to highlight an entire line (from column A to M) of information IF the numbers contained in columns F thru M are greater than 20.

What I tried to do was, I highlighted A thru M and then did conditional formating then chose "Use a formula to determine which cells to format."

Then I put in the box.... =(F4+G4+H4+I4+J4+M4)>20

and I then changed the color to a light gray.

When I enter that, it turns part of the line gray and doesnt really change when I enter values over 20.

Am I doing something wrong or is there another way to do that?

Thanks.

named range? For example, I want a given cell to refer to a named

range. However, the specific named range it would reference is

dependent on today's date. I have named ranges for each weekday, i.e.

Data2, Data3, Data4, Data5, Data6.

In the cell which will reference these ranges, I want to create the

reference to the range as "Data"+ WEEKDAY(TODAY()). I can't find any

docs to describe how to accomplish this.

TIA,

Rose

I have attached a file in which i need some help using a formula to calculate a milage rate once it hits a certain level. So in my sheet the limit for mileage is 10,000 at which these can be claimed at 0.45p and anything thereafter at 0.25p. The opening miles is 9800 which means there are 200 miles left at 0.45p and this is reached on the 4th when 30 miles are done in the day - so i need to calculate 20 @0.45p and10 @0.25p. But i need a formula which will say anything which is under 10,000 charge at 0.45p and anything after that threshold at 0.25p.

I hope this makes sense -this is my first post!

Regards

Andrew.Mileage Rate.xlsx

Here's what I would like it to do.

First, coloring Key: Purple means the report is due that month, green means we've received it.

Say I have four months: April, May, June, July

-If April is filled (with a date) then the cell in June-on the same row-should be colored purple (meaning that June is when the next report is due).

-If May is filled with a date, then the adjacent June cell should be blank (meaning no report is required that month.

-Then, if June has been highlighted as purple because a report is due that month, as soon as a date is entered in, the cell color should change to green.

-And, because I don't want the entire report to be purple until data is entered: If both April and May are blank, then June should also be blank.

-and if possible, if no data is entered after a certain date, the cell color turns red.

I've got the purple to work and be blank if something in the cell before it with the following:

Using a formula to determine which cell to format: =LEFT(F3,1)="" (This is when I'm in cell G3)

How do you have it recognize if something is filled with data without putting a range in, and when there's no data, instead of ""?

Lot's of questions, but only one big one. Any help would be appreciated. I do know that if I did =LEFT(G3,1)="" (again, when in cell G3) it'll turn blank if I type data in G3, but it won't turn blank if there's data in F3.

named range? For example, I want a given cell to refer to a named

range. However, the specific named range it would reference is

dependent on today's date. I have named ranges for each weekday, i.e.

Data2, Data3, Data4, Data5, Data6.

In the cell which will reference these ranges, I want to create the

reference to the range as "Data"+ WEEKDAY(TODAY()). I can't find any

docs to describe how to accomplish this.

TIA,

Rose

No luck finding an answer? You could always try Google.