Excel Conditional formatting and Offset

Question

I cannot get conditional formatting to copy to the right (with paint brush) because excel references the wrong cells when copied. This is what I need to achieve:

A1 needs to be formatted if Sheet2!E4=empty I have conditional formatting formula set as "=isblank(Sheet2!E4)=false" and it works.

Now I need to achieve the exact same formatting for the following scenarios:

A1 needs to be formatted if Sheet2!E4=empty

B1 needs to be formatted if Sheet2!E5=empty

C1 needs to be formatted if Sheet2!E6=empty, etc.

Notice that I'm trying to make excel copy the conditional formatting formula left to the right (columns A-C) but the reference cells are going top to bottom (rows 4-6).

Is there any way to achieve this without having to type the conditional formatting formulas individually? Maybe something with offset included? It's a very large table and I'd never be done. Any input is appreciated!

Answer 1

One way this can be accomplished using setting up a helper row in any of the sheets.

In the below example I have added a helper row in a new sheet - Sheet3, as below. Using this you are essentially transposing to get a reference to the cells in Sheet2 which you would like to use in your conditional formatting on Sheet1.

Next, setup the below conditional formatting formula of cell A1 in Sheet1. =INDIRECT(CONCATENATE("Sheet2!"&Sheet3!A1))=""

In the formula above, the INDIRECT function will help to translate Sheet3!A1 to the contents of A1 which then becomes Sheet2!E4 and evaluate it against a blank value.

Now you can copy formats from A1 to the range of cells across. As you copy across, the normal excel behavior kicks in and replaces A1 to A2, A3, A4 and so on in the subsequent cells. HTH

Answer 2

Option 1

Another way that is similar to Bharat's answer is to make the reference absolute but offset with an an index row that increments by +1 per column.

For a visual, lets say that we have the following in Sheet1:

E3  Data Header [not actual data, just the header]
E4  Apple
E5  Banana
E6  Orange
E7+ [Blank]

This is what you could do with sheet2, you can add a row (make it invisible if you want), start with 1 and increase by 1 per column. Starting with the left most column, put your offset formula in an IF statement and reference the index column. This is where you want to stop dragging/copying from to the right on sheet2, as you drag right, the offset will increase by +1 as your reference increases.

Maybe it's easier to see visually. Below is a visual of Sheet2, I also revealed the data returned so you know the offset is working, and a sample of an embedded offset function in an IF(ISBLANK,TRUE,FALSE) Excel formula. As you can see the system will increment the offset column, but not the reference column, this way with your index, it's increasing down on sheet 1, while moving across on sheet 2.

[Sheet2] https://i.sstatic.net/OZKE3.jpg

---

Option 2

Offset based on the column, you don't need to even create an index row to reference, if you aren't starting from A1, you may need to adjust the column offset accordingly. This also may not work well in all situations.

From Sheet1

=IF(ISBLANK(OFFSET(Sheet2!$E$3,COLUMN(),0)),TRUE,FALSE)