Referencing Visible Cells Only: Filtered Alternating Cell Color

**m.r.quinton** · 06-13-2012, 12:11 PM

Ideally I would want a way to reference the cell one row above my current cell, but only if it is visible on my current filter. Ex
.........A.....B.....C....D
1
4......A1...B1....C1 (Not.....D3)
6......A4...B4....C4

I probably can't explain this well.

I have a group of people. They live in a City, which exists in a State. A state has multiple cities, and a city has multiple people living in it.
And I want to be able to Fill the cells based on location (Combination of City + State) with an alternating pattern for ease of viewing.This block of cells is Sacramento, California. This block of cells is Sacramento, Texas, etc.

I have got it working with sorts. But I can't get it working with filters because the formula refers to the original cell, not the visible cell above.
I've tried do a Subtotal(Sum, CellAbove), but that always returns 0, because I guess Subtotal won't read the resultant value.

Attached is a sample.
Unique State/City is just a Y/N key to remove duplicates. State/City/People/Email is data. Sorting Key is something I just added to get conditional formatting in.

Sheet 1 is whre I began. Sheet 2 shows that if you sort sheet 1 the fill messes up because it isn't conditional.
Sheet 3 is where I am at now. Sorts work. Some filters work. But then if I file for People Miller, the fill color clumps.

**WHER** · 06-13-2012, 02:34 PM

Filter/Unfilter for Miller on Sheet3, see if the conditional formatting works as you intended.

**m.r.quinton** · 06-13-2012, 04:03 PM

That does seem to work. Thank you.

Unfortunately I have brick-walled at another point. I should probably stop, since there is probably already too much information for anyone to actually care about the minute filtering, but I'll keep trying for a day.
For now though, this questions has been answered.