Help with Indirect Conditional Formatting not Adjusting to Changes

[garylundblad]

I love conditional formatting, but I hate that when I insert a column to the left of one that has indirect conditional formatting, the indirect formula doesn't adjust to the change, but rather now looks at the wrong column. I've even tried removing the "$" from the indirect formulas.

Has anyone figured a way around this?

Thank you!!!

Gary

 

[NARAYANK991]

Hi ,

That is how Excel works.

When INDIRECT is used , and you pass it a parameter within quotes , then it becomes a literal string which Excel will not change under any circumstance.

For example , suppose I have the following CF rule :

=INDIRECT("F" & ROW())>50

Since the column reference is within quotes , Excel will not change it when columns are inserted or deleted.

Instead , if the same CF rule is rewritten as :

=INDIRECT(ADDRESS(ROW(),COLUMN()))>50

there is no literal text involved , and Excel will make the necessary adjustments when you insert / delete columns.

Narayan

 

[garylundblad]

Thank you for your reply, but I'm a little confused. Your rewritten example does not include a column reference, so how does it know which column to look at. Am I supposed to insert a column letter in there somewhere?

Thank you Narayan!

 

[NARAYANK991]

Hi ,

The example I gave was for a CF formula in column F itself , which is why COLUMN() itself suffices. If you were to use this formula to refer to column F , and if it were inserted in some other column , you would need to add / subtract a column offset to refer to column F.

Narayan

 

[garylundblad]

Thank you Narayan, but I'm afraid I don't understand what you mean by "column offset." Can you give an example, using an indirect CF in column D that is dependent on column F?

Thank you again Narayan!

Gary

 

[NARAYANK991]

Hi Gary ,

The function COLUMN() , without any reference within the brackets , refers to the column where it is used.

Thus , if your CF formula is anywhere in column D , then COLUMN() will return the value of 4. Thus , suppose your CF formula is for cell D7 , the formula :

=INDIRECT(ADDRESS(ROW(),COLUMN()))>50

will be checking for whether the value in cell D7 is greater than 50.

Now , suppose you are wanting this formula for D7 , but you wish to refer to the contents of cell F12 , then the offsets to be applied are :

Row offset = 12 - 7 = +5

Column offset = Column F - Column D = +2

Thus , the following CF formula , entered with the cursor positioned in cell D7 , will check whether the contents of cell F12 are greater than 50 :

=INDIRECT(ADDRESS(ROW()+5,COLUMN()+2))>50

Narayan

 

[garylundblad]

I think I get what you're saying, at least I seem to be able to duplicate it, but when I insert a row between the CF cell and the cell I'm referencing, the CF still doesn't seem smart enough to adjust the CF formula so it's still looking at the same value. Am I making sense? If my CF is in cell B3 and I want it to be dependent on cell D3 for it's formatting, I would use "=indirect(Address(Row(),Column()+2))>50. This works, but then if I insert a column between B3 and D3, the CF formula does not adjust, and that's what I was trying to accomplish in the first place. Am I missing something?

Thank you for your patience Narayan!

Gary

 

[NARAYANK991]

Hi Gary ,

Please note that the CF formula for what you wish to do does not need to use INDIRECT at all.

Everything depends on what you want to do , so if you can give a proper example of this , it would help , unless your interest is just academic.

If you want to conditionally format cells in column B based on the corresponding cells in column D , as you have mentioned , cell B3 to be based on cell D3 , just place the cursor in cell B3 , and use the CF formula :

=D3 > 50

This will automatically adjust to insertion / deletion of columns. Use the $ sign as required.

The INDIRECT example was given because you mentioned the INDIRECT function ; offhand I cannot think of an example where the INDIRECT function would be required ; if you have any , please post it , and we can look at the alternatives.

Narayan

 

[garylundblad]

I have a lot of workbooks actually that use indirect CF. Usually it's because I don't want to create a lot of formulas, so I can just highlight a large area, or areas and do an indirect that just looks at the corresponding row of a particular column. For example, let's say I want B2:B500 to turn yellow if anything but a zero appears in column D. That's where I've used an indirect CF, but then have to be sure I don't insert a column between B and D, which doesn't usually happen, but when it does it's most exasperating.

I have a form I created for our sales team that was designed to be somewhat idiot proof, as certain information was entered, other required information was highlighted, and incorrect information was flagged as such. The form was amazing until they started changing the rules on me, and then I started running into problems as I tried to redesign the form, one of the problems being my CF.

I am the kind of person that is constantly building, tweaking, and improving custom Excel applications that do the various tasks I have to do. So at one point I have it working beautifully, usually with significant CF, and then I have an "amazing" idea for a change I just have to do, and then there goes my CF.

I really appreciate your assistance, and I hope I'm not frustrating you. If I am, please feel free to move on from this subject- I won't take it personally.

Thank you Narayan!

Gary

 

[NARAYANK991]

Hi Gary ,

The example you have given does not need INDIRECT ! The simplest way would be to refer to column D directly , so that if columns in between column B and column D are deleted , or new columns are inserted , the formulae will adjust accordingly ; after all this is what happens all the time with normal worksheet formulae ; suppose I were to use a normal worksheet formula in cell B3 , such as :

=IF(D3>50 , "Red" , "Green")

this formula would refer to the cell D3 ; now if two columns were to be inserted between columns B and D , Excel would automatically adjust this formula to :

=IF(F3>50 , "Red" , "Green")

so that the cell B3 would still continue to reference the correct column D ( of course now this would be column F , but the contents of the earlier cell D3 would still be the same ).

It would help if you could upload a sample workbook with at least a couple of such formulae , so that it is clear that using the INDIRECT function is the only way to achieve what ever you want to achieve.

Narayan