Sorting rows without messing up cell references??

[ModelerGirl]

I have a several tables that refer to one another. I'd like to sor
some of them, but doing so while keeping the formulas as relativ
references messes up each cell references. If I change all the ro
references to absolute, things get mess up if I sort the original tabl
being referred to.

Is there any way to get around this without manually cutting an
pasting each row around into the right sort order?

Thanks!!

 

[Dave Peterson]

If you can find a unique value in each table, maybe you could change your
formulas around.

Say one table is on sheet1, A1:E99 and your common unique key is in column A.
And you want to use what's in column E of that table:

=vlookup(c13,sheet1!$a$1:$e$99,5,false)

Will return that value.

If the key value is not in the leftmost column of the table, you could use a
different formula:
(say it's in column C and you want to bring back column A, this time)

=index(sheet1!$a$1:$a$99,match(c13,sheet1!$c$1:$c$99,0))

Direct links to other sheets don't work so well when you want to sort it.