Conditional Formatting - Refer to different row depending on whether row number is odd or even.

[Herakles]

I want to be able to refer to a cell in the same row if the formatted cell is in an odd row and to the row above if the formatted cell is in an even row.

How do i do this?

Thanks.

 

[Joe4]

Take a look at the ISODD and ISEVEN functions.

You can use these on the ROW() function, which returns the row number of the row the formula is placed in, i.e.

=ISEVEN(ROW())

 

[Herakles]

Take a look at the ISODD and ISEVEN functions.

You can use these on the ROW() function, which returns the row number of the row the formula is placed in, i.e.

=ISEVEN(ROW())

I've got that bit to work but how do I refer to a cell in the row above only on the even rows?

I really need a non volitile version of Offset.

 

[Joe4]

It would be very helpful if instead of speaking in vague generalities, if you actually give us an example of exactly what it is you are trying to do.
Then we may be able to build a solution tailored to your specific situation, instead of just vague responses to vague questions.

And please explain what exactly you mean here:

I really need a non volitile version of Offset.

and why it needs to behave that way.

 

[Kalim Shaikh]

Go to Home -> Conditional Formatting -> Highlight Cells Rules -> More Rules or New Rules
and Select the last option, i.e. “Use a formula to determine which cells to format”
Inter below formula in (Edit the Rule Description)
“=ISEVEN(ROW())”

Click on the "Format" button to choose the format that you want to apply to the even rows. Select Your Range, I did it only for $A$1:$I$20.
Repeat
Note: Posted the answer based on public interest and pure good faith.

 

[Herakles]

It would be very helpful if instead of speaking in vague generalities, if you actually give us an example of exactly what it is you are trying to do.
Then we may be able to build a solution tailored to your specific situation, instead of just vague responses to vague questions.

And please explain what exactly you mean here:

and why it needs to behave that way.

Please see the image.

I want the formulas in the CF that applies to the range F3:K12 to refer to the red cells on the same row if an odd row and on the row above if it is an even row.

Thanks

 

[Joe4]

Please the Conditional Formatting formulas, so we can help you update it accordingly.
We can not update a formula if we don't know what it is.

 

[Herakles]

Please the Conditional Formatting formulas, so we can help you update it accordingly.
We can not update a formula if we don't know what it is.

Odd rows : =IF(ISODD(ROW()),IF($M3=1,TRUE,FALSE),FALSE)

Even rows : =IF(ISEVEN(ROW()),IF($M3=1,TRUE,FALSE),FALSE)

I need to adjust these.

I'm just trying to understand the method and then I can apply it to a more complicated model.

Thanks

 

[Joe4]

Something like:

=IF(ISODD(ROW()),AND($M3=1,...),AND($M2=1,...))

Because a row number can only ever be EVEN or ODD, you only need to check one condition. If it is not one, it is the other.
AND and OR functions return boolean values, by definitition, so you don't explicitly need to tell them to return TRUE or FALSE.

The Red part of the function is what to do on an ODD number row, and blue is for the EVEN number row.

 

[Herakles]

Something like:

=IF(ISODD(ROW()),AND($M3=1,...),AND($M2=1,...))

Because a row number can only ever be EVEN or ODD, you only need to check one condition. If it is not one, it is the other.
AND and OR functions return boolean values, by definitition, so you don't explicitly need to tell them to return TRUE or FALSE.

The Red part of the function is what to do on an ODD number row, and blue is for the EVEN number row.

Where the row is EVEN I need to refer to the row above in the formula and if ODD I need to refer
to the same row. How do I do that?