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.

=OFFSET($Y6,0,ROUNDDOWN(((COLUMN(Y6)-COLUMN($Y6))/COLUMNS(Header_BOM_Manufacturing_Location_Purchasing)),0)*COLUMNS(Header_BOM_Manufacturing_Location_Purchasing))=0

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.

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

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.

=INDEX(OFFSET(Header_BOM_Manufacturing_Location_Purchasing_All,$A6,0),1,(MATCH(OFFSET($BJ$4,0,ROUNDDOWN((COLUMN()-COLUMN($BJ$4))/COLUMNS(Header_BOM_Manufacturing_Location_Quoting),0)*COLUMNS(Header_BOM_Manufacturing_Location_Quoting)),OFFSET(Header_BOM_Manufacturing_Location_Purchasing_All,-1,0),0)))=0

=AND(COUNTBLANK(BJ6)=0,INDEX(OFFSET(Header_BOM_Manufacturing_Location_Purchasing_All,$A6,0),1,(MATCH(OFFSET($BJ$4,0,ROUNDDOWN((COLUMN()-COLUMN($BJ$4))/COLUMNS(Header_BOM_Manufacturing_Location_Quoting),0)*COLUMNS(Header_BOM_Manufacturing_Location_Quoting)),OFFSET(Header_BOM_Manufacturing_Location_Purchasing_All,-1,0),0)))=0)

 

[drew.j.harrison]

FYI this is cross posted here:

Conditional formula argument limitation or other issue?

 

[barry houdini]

I suspect you are running in to a problem with COLUMN function.

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.

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

 

[drew.j.harrison]

I suspect you are running in to a problem with COLUMN function.

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.

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

Worked like a charm. Would have never figured that out on my own. Thanks for the help!