formula works but not for formatting

Swim

New Member
Joined
Jan 23, 2012
Messages
2
I am trying to conditionally format using AND() to reference if there is a value in the top line of a table and the title in the right most column for each cell in the table, the formula I am using works when i paste it into cells (i get the correct TRUE or FALSE for each cell in the sheet) but is not working as a format formula, I dont get an error but the shading does not happen. any idea's why?

the formula:

=AND(OFFSET($A$1,0,(COLUMN()-1),1,1)<>"",INDIRECT("a"&ROW())="Item1")
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
I am trying to conditionally format using AND() to reference if there is a value in the top line of a table and the title in the right most column for each cell in the table, the formula I am using works when i paste it into cells (i get the correct TRUE or FALSE for each cell in the sheet) but is not working as a format formula, I dont get an error but the shading does not happen. any idea's why?

the formula:

=AND(OFFSET($A$1,0,(COLUMN()-1),1,1)<>"",INDIRECT("a"&ROW())="Item1")
I ran into a similar situation a few weeks ago using AND with INDIRECT in a CF formula.

See if this works...

=(OFFSET($A$1,0,(COLUMN()-1),1,1)<>"")*(INDIRECT("a"&ROW())="Item1")
 
Upvote 0
I ran into a similar situation a few weeks ago using AND with INDIRECT in a CF formula.

See if this works...

=(OFFSET($A$1,0,(COLUMN()-1),1,1)<>"")*(INDIRECT("a"&ROW())="Item1")

Ok, I've figured out why the CF fails on these...

Seems like for whatever reason the AND function when used in CF doesn't like it when functions return arrays.

In your formula both the COLUMN and ROW functions return arrays (even if the array consists of a single element which is what they do in this case).

If we coerce those arrays to scalars then everything is just fine and dandy.

Here's how I did it...

=AND(OFFSET($A$1,0,SUM(COLUMN()-1),1,1)<>"",INDIRECT("a"&SUM(ROW()))="Item1")
 
Upvote 0

Forum statistics

Threads
1,223,246
Messages
6,170,988
Members
452,373
Latest member
TimReeks

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top