Sort operation on table mucks up formulae
Hi there,
I'm hoping there are some smarter people than me here…
There usually are…
I have a table which is meant to list some sales results from different retail shops in descending sales order, and I'm having trouble with a couple of aspects of it.
To put things into context, the table looks like this:
And the following points about the table are relevant:
- The last four columns reference sales data from other sheets in the document.
- The table is ordered by manually selecting "Sort Descending" on the last column.
- The "first" column is actually a separate table up against the main table (so that the descending numbers in that "first" column aren't affected by the "Sort Descending" operation done on the last column).
- The formulae in the top row obtains its information from the last column, and the formulae looks like this:
But the main problem that happens is the following:
Just say that the sales from Store 4 drops from 5.01 units per week down to 1.52 units per week. The table then looks like:
Then, when I do the Sort Descending operation on the last column, it looks like this (Store 4 drops down to its new place in the sales order):
However, doing that Sort Descending operation on the last column somehow mucks up the formulae in the top row, which now looks like this:
And every time the last column has changed values, then a Sort Descending operation done on it, that top row formulae gets more and more muddled up. (I've tried using the Absolute $ in front of the E column references in that formulae, but it doesn't help.)
So my questions are:
1. How do I get that top row formulae to work?
2. Is there a way to have the last column sort itself automatically rather than having to do a manual Sort Descending on it?
3. Is there a way to have a first column of the table that is unaffected by the sort operation on the last column (but the middle columns still change with the last column)?
Any help would be greatly appreciated! 🙂
MacBook Pro, Mac OS X (10.7.2)
