excel gurus

[CJ-95GT]

I assume there is an Excel for Dummies book out there. If not try the help feature.

I should not be hard to figure this one out.

There is actually a website and forum. Sounds like the OP needs an admin with something more than big tits.

[BERNIE MOSFET]

I think what makes this difficult is that conditional formatting rules are, each, limited to iterative application across a range of cells, conditional on a single true/false case from the value of an individual cell or the resultant value of some operation on a range of cells.

Essentially, because you don't have a finite number of possible values in column A, you are trying to solve two variables with one equation - which can't be done.



If the values in column A are or can be sequentially propagated through a formula and contains numeric characters only, you can do something like this:

Format Column A to round to whole numbers.

A1: =SUM(SUM(M1,(IF(G1="Done",0,1))*0.15),1)

Where M1 can be a starting number, does not need to point to a cell.

A2: =SUM(SUM(ROUND(A1,0),(IF(G2="Done",0,1))*0.15),1)

A3: =SUM(SUM(ROUND(A2,0),(IF(G3="Done",0,1))*0.15),1)

And so on...

Then establish a rule for Column A: "Format only cells that contain" with "specific text" "Contains" ".15" [or some decimal number lower than .5 for rounding to work] that will never appear again in the sheet.

This will allow the number you want to see to be visible, and will highlight red when the corresponding value in cell G is not "Done"



If not, if the values in column A are manually entered or are alphanumeric, why not just insert an indicator column?

[Gtracer]

I think what makes this difficult is that conditional formatting rules are, each, limited to iterative application across a range of cells, conditional on a single true/false case from the value of an individual cell or the resultant value of some operation on a range of cells.

Essentially, because you don't have a finite number of possible values in column A, you are trying to solve two variables with one equation - which can't be done.



If the values in column A are or can be sequentially propagated through a formula and contains numeric characters only, you can do something like this:

Format Column A to round to whole numbers.

A1: =SUM(SUM(M1,(IF(G1="Done",0,1))*0.15),1)

Where M1 can be a starting number, does not need to point to a cell.

A2: =SUM(SUM(ROUND(A1,0),(IF(G2="Done",0,1))*0.15),1)

A3: =SUM(SUM(ROUND(A2,0),(IF(G3="Done",0,1))*0.15),1)

And so on...

Then establish a rule for Column A: "Format only cells that contain" with "specific text" "Contains" ".15" [or some decimal number lower than .5 for rounding to work] that will never appear again in the sheet.

This will allow the number you want to see to be visible, and will highlight red when the corresponding value in cell G is "Done"



If not, if the values in column A are manually entered or are alphanumeric, why not just insert an indicator column?

**Golf Clap***

You make is sound soo...easy lol

I hope I can just come up with this type of $hit one day

[BERNIE MOSFET]

[Nash B.]

I think what makes this difficult is that conditional formatting rules are, each, limited to iterative application across a range of cells, conditional on a single true/false case from the value of an individual cell or the resultant value of some operation on a range of cells.

Essentially, because you don't have a finite number of possible values in column A, you are trying to solve two variables with one equation - which can't be done.



If the values in column A are or can be sequentially propagated through a formula and contains numeric characters only, you can do something like this:

Format Column A to round to whole numbers.

A1: =SUM(SUM(M1,(IF(G1="Done",0,1))*0.15),1)

Where M1 can be a starting number, does not need to point to a cell.

A2: =SUM(SUM(ROUND(A1,0),(IF(G2="Done",0,1))*0.15),1)

A3: =SUM(SUM(ROUND(A2,0),(IF(G3="Done",0,1))*0.15),1)

And so on...

Then establish a rule for Column A: "Format only cells that contain" with "specific text" "Contains" ".15" [or some decimal number lower than .5 for rounding to work] that will never appear again in the sheet.

This will allow the number you want to see to be visible, and will highlight red when the corresponding value in cell G is not "Done"



If not, if the values in column A are manually entered or are alphanumeric, why not just insert an indicator column?

Way too complicated.

[BERNIE MOSFET]

Way too complicated.

He's asking for something that Excel doesn't have built into it. How else do you cram two values into one cell?

[Nash B.]

Use another cell.

[Silverback]

He's asking for something that Excel doesn't have built into it. How else do you cram two values into one cell?

with lube

[BERNIE MOSFET]

Use another cell.

If the rules allowed for the value of one cell to determine the formatting of another, sure, use another cell. As I'm seeing it, the rules look only at the value of the cell being conditionally formatted. That value must contain or be subject to some true/false condition. If, in the rule formula, the true/false condition is determined by the value of some other cell, all cells in the applicable range are determined by the same other cell. What happens is that G1 determines A1, A2, A3, and so on where the desired result is G1 determines A1, G2 determines A2, etc... There is not a provision in the rules to index the application of the rule, so the true/false condition must be contained wholly in the value of each cell being investigated when the rule applies to a range of cells.


Or lube.

[BERNIE MOSFET]

Or, reading the original question again, it would appear he wants all of column A lit up if Done isn't in any cells of Column G.

[Nash B.]

If the rules allowed for the value of one cell to determine the formatting of another, sure, use another cell. As I'm seeing it, the rules look only at the value of the cell being conditionally formatted. That value must contain or be subject to some true/false condition. If, in the rule formula, the true/false condition is determined by the value of some other cell, all cells in the applicable range are determined by the same other cell. What happens is that G1 determines A1, A2, A3, and so on where the desired result is G1 determines A1, G2 determines A2, etc... There is not a provision in the rules to index the application of the rule, so the true/false condition must be contained wholly in the value of each cell being investigated when the rule applies to a range of cells.


Or lube.

What version of Excel are you using? There's "Use a formula to determine which cells to format" in 2010. The name is misleading, though, as the selected cell doesn't have to be included in the criteria/formula.

[BERNIE MOSFET]

2010, I've been looking at the same thing.


Edit: It's kind of moot. For some reason I got the idea that he wanted individual cells to highlight red, not the whole column.

[bonnie&clyde]

Or, reading the original question again, it would appear he wants all of column A lit up if Done isn't in any cells of Column G.

i want A2 to red up if G2 doesnt have Done in it, and A3 to red up if G3 doesnt have Done in it and so on and so on

[Nash B.]

That's not what you said in the original post! That makes it easier, though. Add another column that =IFERROR(SEARCH("done",Gx),0)>0 where x is the row number (G1, G2, G3, etc.). Then select column A, add a new Conditional Formatting Rule, select "Use a formula to determine which cells to format" and set it to =<Column>1, where <Column> is the column you put the true/false values in.

[BERNIE MOSFET]

That's not what you said in the original post! That makes it easier, though. Add another column that =IFERROR(SEARCH("done",Gx),0)>0 where x is the row number (G1, G2, G3, etc.). Then select column A, add a new Conditional Formatting Rule, select "Use a formula to determine which cells to format" and set it to =<Column>1, where <Column> is the column you put the true/false values in.

But that highlights the column with the true/false values, yeah?