New answers tagged

1

Select the data from C2 to N13. Create a new conditional formatting rule with a formula. Use this formula =C2<$B2 and apply a format. Note the placement of the dollar signs. As the conditional format is applied across and down, the reference C2 will be adjusted to whatever the current cell is. The comparison will always look at column B, since the $ ...


0

Bearing in mind the previous advice that white font may be visible on PDF printing, I use the following technique: Use a cell outside the print area with a "1" or "0" entry. I then refer to this field in the conditional formatting of all cells required not to print. If A1=0 then set format to white font. If A1=1 then set format to black or any colour ...


1

You need to use a formula for the conditional format and combine the two conditions: column L in the same row is equal to your trigger text, for example "yes" the combined cells of M, N and O in the same row must have a text length of 0 That can be expressed with the formula =AND($L2="yes",LEN($M2&$N2&$O2)=0) See screenshot for an example. The ...


2

Here is an answer with two helper columns (of course you can hide them): helper1: =AGGREGATE(2,5,A2) it just shows 1 for visible and 0 for invisible rows (of course you always see 1 :) ) helper2: =IF(C2=1,IFERROR(MAX($D$1:D1)+(COUNTIFS($A$1:A1,A2,$C$1:C1,1)=0),1),"") MAX($D$1:D1) - looks for greatest group number so far COUNTIFS($A$1:A1,A2,$C$1:C1,1) -...


1

Use SUMPRODUCT and LEFT or MID to find the values and sum: =SUMPRODUCT(--LEFT($A$1:$A$3,FIND("/",$A$1:$A$3)-1))&"/"&SUMPRODUCT(--MID(SUBSTITUTE($A$1:$A$3,"/",REPT(" ",99)),99,99))&"/"&SUMPRODUCT(--MID(SUBSTITUTE($A$1:$A$3,"/",REPT(" ",99)),198,99)) The substitute is splitting the three numbers by 99 spaces so we can estimate where the ...


1

The helper column alternating 1 and 0 does not need a MOD() function. A simple formula for E2 is - =IF(B2=B1,E1,1-E1)


1

It’s much simpler than the other answer suggests.  Simply select the entire floor plan worksheet (i.e., Sheet1) and create a conditional formatting rule.  Use a formula to determine which cells to format: =VLOOKUP(A1, Sheet2!$A$1:$D$203, 3) = "Empty" For each cell, which represents a storage unit, this takes the number in the cell, which is the number of ...


0

I thought it was tougher than this, but it turns out to be quite simple. Create a new field, a new column in Sheet2. Name it Map, which relates to your Graph Sheet. Your first entry will be a formula for unit 1. Formula for unit 1: if(c2="Empty", True, "") One by one. Start with Unit 1. Select Unit one's cell, or cells, then add a conditional format ...


2

You're on the right track! The first condition in your AND function is correct. But for the second one, you need to specify which cell it should compare "" with. "" by itself is not a condition. Try this (assuming your conditional format is being applied to a range of cells starting at cell A2): =AND($B2="completed", A2="")


1

You can add more condition to your And for Red, O42<>"N/A",O42<>"None" your formula will look like the following: =AND(NOT(ISBLANK(O42)),O42<>M42,O42<>"N/A",O42<>"None") The cell will turn Red when all the condition are true


0

If I've understood correctly, just modify you're second conditional format forumal: Therefore, this: =AND(NOT(ISBLANK(O42)),O42<>M42) Becomes: =AND(NOT(ISBLANK(O42)),O42<>M42,O42<>"N/A",O42<>"None") If you break this down to English, it reads something like this: Red will be applied (as per your conditional formatting)... if ...


0

There are multiple ways to do this. Continuing with the style of what you already have, go add 2 new rules, Both are for specific text, one uses N/A and the other uses None for the text. Do not set any format or set the format that you want for a N/A. Then select stop if true and make sure it is before the red format conditional. This will make it so the ...


0

Create a range of cells you want to clean up, then try something like this. if instr(rng,".??") > 0 then rng.NumberFormat = "#,##0.00:-#,##0.00" If you want to provide a little better information about your data structure, my answer would be more specific to your problem.



Top 50 recent answers are included