Nested dynamic range conditional formatting based on merged cells? I think??

Thortok2000

New Member
Joined
Oct 1, 2019
Messages
3
This one is giving me a headache. I've been learning a lot about excel lately through googling and such but I can't figure this one out myself.

I feel like what I want is simple. I want conditional formatting, 3-color scale, with the midpoint based on the average value in the range.

The problem is I want multiple ranges in one column. In column A I have some merged cells with names: "banana, telephone, susan"

So in the column with the numbers I want to have the conditional formatting on, I don't want the conditional formatting to be of the entire sheet, I want a separate midpoint value for banana, telephone, and susan.

Reason is banana might have an average of 30 thousand and susan might have an average of 25. I don't want everything in 'susan' to be marked as 'low'...I want 'susan' to have her own average based only on her data.

Am I making sense?

Here's the worst part. banana, telephone, susan...when I update the sheet they get a different number of rows. I pull a report for banana, then in column A I merge and combine and label it banana. Then I pull a report for telephone, merge and center, and then pull a report for susan, merge and center. So instead of just 'locking in' multiple different conditional formattings each with their own range, the range has to change because the number of rows might change any given day.

I was able to come up with a neat trick for a 'helper' column that I can hide, to replace column A, and if the cell in column A on the same row is blank, it copies the cell above it.

But at this point I've been learning about INDEX, VLOOKUP, COUNTIF, SUMPRODUCT, AVERAGEIF, MATCH, and I'm just lost.

At some point I learned that conditional formatting can't be assigned to a dynamic range? So my next thought was making another 'helper column' which returns the average value over and over again and the conditional formatting would just refer to that column for its midpoint value.

But I don't know how to make that helper column.

Is anything I'm saying making any sense? I give up.
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
5,617
Office Version
365
Platform
Windows
One option

Consider using 3 tabs
- banana
- telephone
- susan
 

Thortok2000

New Member
Joined
Oct 1, 2019
Messages
3
One option

Consider using 3 tabs
- banana
- telephone
- susan
The sheet I'm making is actually a template that I then fill out and copy as a tab onto another sheet every week. The reason I want it dynamic is to try to save me some time in making it every week instead of having to clear the format and then reapply it based on the new number of rows each category has.

There's also many more than 3 categories, I was trying to simplify for the sake of phrasing my question. =(

But thanks though!
 

Thortok2000

New Member
Joined
Oct 1, 2019
Messages
3
I can't edit my posts or I would add this in to my last reply.

I'm basically looking for a formula that will do something like this:

Check column U on the same row to see what's in it (this is my helper column for column A where I have the 'merge & center' on the left, it's a hidden column)

Column E is where the averages are. So I want to return an array of all the values in E where the U of that row equals the U of this row. Then I want to perform an 'average' on it, and put that value in V of the row that was originally checking column U to start with.

Then I realized I need another helper column for 'min' and 'max' as well but that should just be two more helper columns with the same formula and just change 'average' to min/max.

Then I want to set the conditional formatting to use the min, max, and midpoint values from the helper columns on any given row.

I have no idea how to do any of this.
 

Watch MrExcel Video

Forum statistics

Threads
1,095,173
Messages
5,442,828
Members
405,199
Latest member
mkarnout

This Week's Hot Topics

  • Copy entire row if CountA <>0 to another sheet
    [B]I want to copy entire row if CountA <>0 for column J7:AM7 (headers on J6:AM6) and so on till the last used cell is column D and paste the...
  • Select last used Row in Table
    I have created a Table in a Worksheet which is locked to prevent user errors and protect formula. Some of the cells require freetext entries which...
  • excel workbook: do not allow certain file name
    Hello all, Don't think this has ever been asked before, but how do I restrict file save [Before_Save Event] if the name of the file being saved...
  • fixing problem autofilter
    hello i need help about my code when i search by code in textbox it doesn't show anything this is my data [ATTACH type="full"...
  • “Weight”
    Hi, i’ve got a long sheet filled with weights such as kg,g,L & ml. i can build a formula to convert kg into g and liter into ml. How ever, my...
  • How to capitalize everything before a certain character?
    In column A, I have some text: Hello good day.mp3 Hello good day.flac etc. I'd like to capitalize everything before the period. I don't need the...
Top