Sorting data with cell references

D

Danny@Kendal

When sorting rows with formulas that refer to other rows
is there a way to make sure these formulas refer to the
referred cell's new location?

eg:
cell B10 formula is "=B1"

after sorting in reverse order,
row 10 becomes row 1
and row 1 becomes row 10

The new formula should now be "=B10"
instead it tries to refer to a negative row value.

Any way to fix this? It's Excel2000 on XPpro.
Google returns "create your formulas after you've sorted the data" which is
not helpful.

I was working on a large index table which has lots of cross-references.
After sorting the table I found most of the references were either broken or
wrong so I had to highlight all the formulas and go through checking them
all. Next time this will all be done automatically and hopefully with a lot
less trouble by Adobe InDesign.
 
D

Danny@Kendal

Drat! Thanks for the reply.

It's probably do-able with scripting but....

Ah! <ding>
Use VLOOKUP to automatically find the cell instead of referencing it
directly.
Sometimes a problem's solution involves a good night's sleep and a fresh
start.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top