Contributor

# Need Help - Conditional Formatting based on another criteria range (column's last cell with data)

Greetings everyone,

I have a decision matrix tool that dynamically expands columns/headings based on another sheet's (Pros vs. Cons) entries. I do not understand why the Conditional Formatting I use for this works in two separate parts but not as a complete formula.

The headings under "Ratings" are a single spill formula using the =FILTER() function that only shows values that I've checked a checkbox form for on the Pros vs. Cons sheet. When I check the box, it will display the name of what I was weighing the pros and cons for under "Rankings".

All I want for row 3 (Rankings) is to 1) be blank if there's no data in row 4, 2) have a right border with formatting if the right-most column of row 4 is the last one, and 3) have formatting if there's any data for row 4.

Current Conditional Formatting Formula (row 4 is blank)

``=ISBLANK(OFFSET(INDIRECT(ADDRESS(3,COLUMN())),1,0))``

Current Conditional Formatting Formula (row 4 contains the last column of data)

``=ISBLANK(OFFSET(INDIRECT(ADDRESS(3,COLUMN())),1,1))``

Current Conditional Formatting Formula (row 4 has data)

``=D\$4<>""``

Broken Conditional Formatting Formula (row 4 has data, and if the last column, have right border)

``=AND(NOT(ISBLANK(OFFSET(INDIRECT(ADDRESS(3,COLUMN())),1,0))),ISBLANK(OFFSET(INDIRECT(ADDRESS(3,COLUMN())),1,1)))``

I would be very grateful for any solution to make this Conditional Formatting formula work (to consolidate the first two rules). Thank you!

2 Replies

# Re: Need Help - Conditional Formatting based on another criteria range (column's last cell with data

1. Is it possible for you to post the actual spreadsheet (or an anonymous facsimile) on OneDrive or GoogleDrive, posting a link granting access to it here? That would help greatly in deciphering what you're seeking help on.
2. Your formulas make reference to "ADDRESS" but you don't explain what that is (and it appears to be a significant part of the picture)
3. You refer to row 3 and call it "Rankings" yet there doesn't appear to be any correlation between that description at the third row of the spreadsheet depicted in the image