Thread: List sorting problem with external cell references

Hi All,

I'm not totally sure which forum is most appropriate for this question, so hopefully I chose the right one.

I have a list that I'm trying to create. This list has four sets of columns. The cells in the "Mode Freq." column reference both the P, Q, and R cells in their respective rows as well as the numerical cells in the top three rows to make its calculation.

What I'd like to do is create a list out of the data in B4 through E31 (the data in columns P, Q, R, and Mode Freq.). I can do this fine.

The next thing I want to do is to be able to sort the mode frequencies, either ascending or descending. When I sort the list the Mode Freq. cells do not continue to "look" at their original three cells at the top to gather their information.

For example, if during sorting, one of the rows was moved down five rows, the Mode Freq. cell is now looking to cells B6, B7, and B8 (instead of B1, B2, B3) and the calculations are all messed up. Is there a way to make this list sortable?

Below is a link to the file.

http://www.bicyclerecordings.com/various/RoomModes.xls

your example image does not exist.

It would be better if you posted an example file rather than a picture

You need to anchor your references to B1,B2 and B3 using $ so for example your formula in E5 copied down would be

=565*SQRT((B5^2/$B$1^2)+(C5^2/$B$2^2)+(D5^2/$B$3^2))

. Whether you drag down or sort, those will be absolute references and won't change. Is that what you are looking for?