How do you sort cells with formulas in them? 1

[Christine74]

I'm creating a spreadsheet using Excel 2000 that runs calculations based on dozens of variables that must be inputted, and calculates dozens more constants that need to be printed out.

The problem I'm having is that the variables you need to input are dispersed throughout the spreadsheet along with the calculated constants. I didn't think about that when I started.

I would like to use the SORT function, or some other function, in order to put all of the input variables in one place, at the beginning of the spreadsheet. I need all of the formulas to reference values from the same cells after I haved moved them.

But when I try to sort cells with equations in them, all of the formulas get messed up.

Any ideas?

Thanks,

-Christine

 

[GregLocock]

I write that sort of spreadsheet too.

What I do is to prepare a nice clean area with plenty of room for documentation, and then click on each variable cell, which surrounds it with a thick border. Now click on that border and you can carry the cell to the new area, with all its links intact.

When I am feeling disciplined I set up a new worksheet before writing the rest of the spreadsheet, that has all the inputs and documentation on it. This makes it very easy to hide the workings of your spreadsheet from prying eyes, if you wish to hide some grubby code.


Cheers

Greg Locock

 

[Christine74]

Yep, that was easy enough.

Thanks!

-Christine

 

[Mutt]

If you should ever want to sort cells (using data-sort) with formulae in them, I just make sure the references are in $A$1 format - then as they move around, stay fixed.