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