Sorting with formulas

[barmacost]

I have a problem that is driving me crazy because I know there must be something simple I'm missing but I'm working on a data sheet for capacity planning that has 29 columns with the intention that it feeds into a pivot table. Here's a breakdown of the setup:


Columns A-D contain basic information such as department, title, etc.

Column E is a column for staffing drivers (calculations in columns F-AC use this column to run formulas against)

Columns F-AC these are the monthly columns where the calculations take place. Because each row represents a different title the calculations for each row are going to be unique


The problem is that no matter if I setup absolute references or not when I sort by any of the columns invariably some of the rows calculations are broken. Does Excel not allow you to sort with formulas that aren't consistent?

 

[Hui]

Barmacost

You need to use Relative and Absolute references carefully, but it can be done


Use Realtive references where data is being referred to, that will always be in the same relative position before and after sorting (ie: always in the same row or always 2 rows above)


Use Absolute where the relative position is likely to change after sorting, ie: Outside the area being sorted, in a row which wont be in the same position after sorting, in a fixed cell somewhere, or a Range used in a Sum(), Lookup() etc


without specific details a few suggestions

- Are you sorting all columns

- Is there data in any columns being sorted that refers to areas outside the area being sorted.

- Instead of refering directly to a cell that is being sorted or not, can you use a lookup or index/match that will adjust itself as it is sorted