Conditional formula argument limitation or other issue?

[drew.j.harrison]

Ran into an odd problem where I have 2 conditional formats. The first one greys out a product row for a specific set of location columns if the volume for that product in that location is 0. Note that the code is a bit convoluted as I am using dynamic references to define the cell to look for the volume in.

Please Login or Register  to view this content.

This code works just fine.

I then tried to add an additional condition that makes the cells red if the greyed out cell is not blank but it doesn't seem to work.

Please Login or Register  to view this content.

I've used the exact same strategy else where with no problems.

Any idea what is going on here? I suspect there is a limit on the references or arguments allowed to define a conditional format that I crossed over.

Below is an example of similar formulas working just fine. Appears to have just as many arguments/references as well if not more.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

FYI this is cross posted here:

http://www.mrexcel.com/forum/excel-q...ml#post4214462

[newdoverman]

If you want the opposite of your first CF rule where the end of the formula is =0 . Why not try replacing the end of the formula with <>0 in a new rule.

Please Login or Register  to view this content.

[drew.j.harrison]

If you want the opposite of your first CF rule where the end of the formula is =0 . Why not try replacing the end of the formula with <>0 in a new rule.

Please Login or Register  to view this content.

I'm not exactly looking for the opposite. I just want to highlight cells in red if there is data where there shouldn't be. It should be blank and greyed out if it the volume for that location = 0. If a user accidentally puts data there or the volume goes to 0 I would like it highlighted in red so the user is notified of a change or their error.

[newdoverman]

If you post your workbook, perhaps this can be solved. Your formula is using defined names of unknown (to us) purpose.

I can see from your two provided formulae that the AND version must have both parts of the AND to be TRUE in order for the formatting to be applied. This part COUNTBLANK(Y6)=0 means that Y6 must have a value in order to be TRUE and the rest of the formula which =0 must also be TRUE. Is it possible for those two parts to be TRUE?

[drew.j.harrison]

Was just solved on the cross posted thread. It was an issue with the Column function returning an array rather than a number.

COLUMN function returns an "array" even when it's only a single value, e.g. {1} rather than 1 and in some contexts some functions may not handle that as required. The normal solution is to enclose the COLUMN functions inside another function like SUM or MAX which will convert the array to a single value, e.g.

Simply adding a SUM function around the column operation solved it.


=AND(COUNTBLANK(Y6)=0,OFFSET($Y6,0,ROUNDDOWN((SUM(COLUMN(Y6)-COLUMN($Y6))/COLUMNS(D2:D2)),0)*COLUMNS(D2:D2))=0)

[newdoverman]

Thanks for the feedback. I was looking at that part of the formula and came to the conclusion that there were very limited situations that would produce 0 as a result. If the SUM fixes the problem ....GREAT!

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.