Sorting data with formulas

[Laura]

I've got a coworker trying to define formulas from data
while it is sorted in one way, then resort the data.
However the formulas are not redefining the cell
references when they move. It's keep the cell reference
used in the original definition. We've tried absolute and
relative references, we've tried to define a total without
using "SUM=(xxx)" but the system seems to do that
automatically even if we enter (A12+A14+A16).

Is there anyway to do this? Thank you for any assistance.

 

[Frank Kabel]

Hi
Excel always link to cell references and not to contents. What you
could try is to use VLOOKUP if you have a unique identifier in one
column.. You may provide some more details about the functions you're
currently using and your data structure

 

[Bernd]

I think you might be able to achieve this with INDIRECT(),
INDEX(), RANK(), OFFSET() etc.

If you seek for more than
http://www.bplumhoff.de/software/efficiency_analysis.xls
please state an explicit simple problem sample.

Disclaimer: Use above mentioned file at your own risk.

Regards,
Bernd