Cell Formula Corruption with sort

[Darold Smith]

I am using text in cells in Column A as references in other cells in other rows.  After sorting the table by the values in Column A, the references (cell formulas) are incorrect.  Is this an inherent limitation in the sorting process or is there a way to work around it?  The issue is the same in Excel 2007 and 2013   The figures are the original table and the table after sorting on Column A.

For example, in the first figure, the reference to A5 in the Position row is used as a reference (formula) for the label in A4 (Longitude). 

The second figure is after sorting the table, the Position row is now row 7 and the label for the reference to the Lattitude row is now A8 instead of the expected A5.  After sorting, all three of the reference cell formulas are now to blank cells (A8, A9, and A10).  There are no error messages or other indications of bad data use.

What is the cause and is there a way to make it work correctly?

[#2]

Hello,

this works as designed. The relative cell references are moved to another row, so they now the reference changes. With a formula like =A5 in cell C4, it really means "two columns to the left of my current position and one row down". When the cell is moved due to sorting, it ends up in row 7, and "two columns to the left of my current position and one row down" is A8.

If you use $ signs to make the cell references absolute, that will not work either, since after sorting, the cells don't have the same order.

I'd like to know: If you want the words "Longitude", "Latitude" (it only has one t after the a, not 2) and "Elevation" in that row, why don't you enter the text verbatim? Then it won't change when you sort the data. 

The only other option is to sort the data first, then enter the cell references. You could write a formula in each cell that searches for the word "Longitude" (and latitude and elevation), but it would be easier to just write "Longitude" into the cell in the first place.

cheers, teylyn

[#3]

Thanks for your prompt and lucid explanation of how the sort engine is implemented.  Also for catching the spelling error.

I want to construct a data dictionary to support Data Flow Diagram methodology diagramming (Structured Analysis, Tom DeMarco).  Data flows may comprise multiple sub-data flows (basically a container) or be a leaf which is a defining element with no children. 

The result is basically instances of hierarchical data definition structures where any data flow can be a component of one or more other data flows.  

Thus, in the example, Position comprises Longitude, Latitude, and Elevation.  By using cell formulas as references to the lower level components, these components can be referenced in many places and if the data flow name is modified, it is automatically updated everywhere in the dictionary.

This is an experiment to see if excel can be used as a "data base" for data flow names to be referenced to data flows in data flow diagrams created in Visio.  

Clearly, I should be able to manually create the such a spreadsheet in alphabetic order and insert new entries in the proper location and manually insert the formula references where they are needed.  It might not be too bad if taking advantage of the split screen capability of Excel.  

I have considered the possibility of using Access but from what I understand is that trying to implement hierarchical structures with tables and relations requires arcane processes and is not intuitively obvious to the users.  

Unfortunately, there are the few DFD tools that support the methodology and they simply create pictures with no underlying information.   

If you have any insight on this issue and have suggestions, please feel free to let me know.

Regards,

Darold Smith

PE Software & Electrical Engneering

Certified Systems Engineering Professional (INCOSE)

[#4]

Hello,

your post appears to be in English, but I haven't really understood what you're saying. 

If you can post a sample file and explain in context, that would be great. Provide the input, mock up the desired output manually and then explain the logic that leads to the output. It would be great if you could use cell references instead of unfamiliar (to me) concepts. So, something like "if A1 changes from 'x' to 'z' cell B2 should display 'y', because 'y' is a result of the rule that [...].. and so on.