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

Contributor

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".

Capture.PNG

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

@Kendethar 

 

  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

 

So I think some clearer explanations of what we're looking at, what is desired, and access to the actual spreadsheet would help us help you,.

Pardon me. I added a picture that shows the Excel reference headers. I hope that encompassed your questions 2 & 3. For question 1, please see the private message.

(Please reply any solution here for the public to see and for your Best Response credit.)