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

[Thortok2000]

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.

 

[Yongle]

One option

Consider using 3 tabs
- banana
- telephone
- susan

 

[Thortok2000]

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]

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.