Excel2002: Data > Sort ruins links! Any suggestions?

[Shiperton Henethe]

Hi

Problem: if I do a sort on one page (ie worksheet)
then all the other pages taking info from that page
will now point to the wrong cells!

This problem happens both if I use ordinary addressees
and also if I use defined "names" instead!

Any suggestions?


Ship

 

[Max]

Think you'll need to use for e.g. VLOOKUP's or OFFSET(..MATCH()...)
in the dependent sheets rather than just simple links to cells in the
"master" sheet

This arrangement will work if you have a key column that is shared between
the dependent sheets and the "master" sheet with no duplicates in that
column.

hth
Max

 

[Shiperton Henethe]

Thanks!
But does the data in the key column have to be sorted
in that order fir VLOOKUP() and/or MATCH() to work?
(The examples in the msExcel help file rather imply this!)

Because if so that is no good! We need to have the
master sheet with a new product on each row, and
we need to lookup each product's "product code"...


Ship

 

[Max]

No need for the data in the key column to be sorted,
provided each product's "product code" is unique

Use "False" or "0" as the range_lookup in VLOOKUP
or "0" as the match_type in MATCH() for an exact match

 

[Shiperton Henethe]

Brilliant - it works!

Only one *small* problemo. I tried hypertext linking
our product codes (ie sheet names)
back to the individual product sheets, and that
works fine, until you rename the sheets (which we
will have to)!

A work-around appears to be to name an individual
cell (e.g. A1) on each sheet with some random name,
but it's a shame micro$oft couldnt be clever enough to change
then names if the sheets in the hypertext links
at the same time as the sheet names are edited...

Ah, well.

With thanks


Ship
Shiperton Henethe

 

[Max]

You're welcome, Ship!

cheers
Max