Excel2002: Data > Sort ruins links! Any suggestions?

  • Thread starter Thread starter Shiperton Henethe
  • Start date Start date
S

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
 
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
 
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
 
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
 
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
 
Back
Top