Sort Is Creating Circular Reference

Hello All,

I have a rather large amount of data that I am sorting by row (not column) and the sort is creating a circular reference. I have identified what is happening but I can't figure out how to do it the way I need to.

I have 30 organizations and for every organization I have 14 columns with various types of data, the rows are dates. That translates into 420 columns which are currently grouped in order of organization first then by data type (column b = ORG A DATA A, column c = ORG A DATA B, column P = ORG B DATA A, etc.). I found that for making my charts that it was going to be much easier to have the columns ordered by data type and then org (column b = ORG A DATA A,column c = ORG B DATA A, column AE = ORG A DATA B, etc.). I have org as row 1 and data type as row 2 so I tried to simply sort the data by row 2 instead of row 1 but when I did it created circular references.

What is happening is that I have a couple of simple equational columns which sort incorrectly. G3 (and the rest of column G respectively), for example, =B3/L3 (ORG A DATA A / ORG A DATA K). After the sort, column G goes to EV and =EQ/FA, which is the same number of columns that B and L are away from G respectively. The problem is that the data for ORG A DATA A is still on column B and the data for ORG A DATA K is now on column KT, which is causing circular and incorrect references. What I need is for the sort to maintain the cell reference to the appropriate data and not a column which is the proportional distance away.

I hope that I have explained myself well. I look forward to your responses. Thanks in advance.

-
JSM

Hello SixthSense, Thanks for your reply.
I am however not sure if I understand it correctly.

You wrote: "Don't Use Sort Field Column For Formula Column."
This means to say that I shouldn't use sort with formulas? - since last night I did some reading and found that sort seems to work on the rules of copy and not of move, this would explain the problem I am having. Is there really no way to sort and MOVE the data, maintaining the cell references? That seems like a fairly simple function to be left out. If I can move it manually without a problem, why can't I automate the 420 moves?

You wrote: "Convert the Formula Result as values and try sorting"
This means to say that I convert the formula into the resulting value (using special paste for example)? - This doesn't work for me as I need this spreadsheet to continue calculating as time goes on. Converting to values would break the formula and stop the calculation.

Thanks a lot guys, I look forward to hearing from you.