Sorting issue

[Linda Broyles]

I have a sheet in which one cell refers to cell in 2nd sheet. Can I write
the formula in such a way that even if the cell in the 2nd sheet moves with a
sort, the cell in the 1st sheet will follow that change and still pick up the
right information?

Ex:
Sheet 1: C2 starts out =Sheet2!E2754

Then someone sorts Sheet 2 so E2754 is now E415.

So, what I want is that when that sort happens Sheet 1 changes C2 to
=Sheet2!E415.

Make sense?

Thanks so much!

 

[Stephen]

I have a sheet in which one cell refers to cell in 2nd sheet. Can I write
the formula in such a way that even if the cell in the 2nd sheet moves
with a
sort, the cell in the 1st sheet will follow that change and still pick up
the
right information?

Ex:
Sheet 1: C2 starts out =Sheet2!E2754

Then someone sorts Sheet 2 so E2754 is now E415.

So, what I want is that when that sort happens Sheet 1 changes C2 to
=Sheet2!E415.

Make sense?

Thanks so much!

What defines this as the "right information"? If you can explain this, maybe
someone can help with a formula.

 

[Linda Broyles]

I am a shipping coordinator so I get rates from shippers from one port to
another. Here's what I am doing:

My first sheet I have a tab with all the Boston rates to all the different
ports we ship to. So I have a row that has Alexandria, the lowest shipping
rate (from the 2nd sheet) and then have a formula to compute how much to
charge our customer per cubic meter.

In the second sheet (different document) my columns are: To, From,
Shipper, Rate (they have really cool drop down boxes that my boss created)
So I select a to & from so I have all my shipper's rates for a particular
route. I then have a row in which I manually put: to, from & the lowest
rate. This is the "right information"

I protected the first sheet, because I can't have anyone messing up the
formulas since the brokers use this information to quote prices. The second
sheet though, is not protected because several of us update the shipping
rates as we receive them.

So the problem is that people sort the second sheet to find the rates they
need and therefore messes up the 1st sheet.

Does that make better sense?


Linda