maintain worksheet reference

  • Thread starter Thread starter inigo45
  • Start date Start date
I

inigo45

Hallo,
How are the cell references maintained after sorting the reference
cells.
Well, an example:

In a workbook are two sheets, Sheet1 and Sheet2. Sheet1 is a ordinar
listing with a subsequent cell reference to Sheet2, like
A.....12%.....Sheet2!G23
B.....33%.....Sheet2!G71
...
Sum...100%.

Sheet2 contains a continuously extended listing and should be sorte
A>Z after new entries are added. I tried absolute and relativ
referencing, neither worked as it should. That is, if e.g. in the abov
example G23 moves to G14, it does NOT change in Sheet1! Absolut
referencing is nonsense as you might suggest.
Any ideas?
Juerge
 
Just some thoughts ..
A.....12%.....Sheet2!G23
B.....33%.....Sheet2!G71
Click to expand...

Assume what you have currently in C2:C3 is:

=Sheet2!G23
=Sheet2!G71

If the linked cells are uniquely associated with the text in A2:A3, and the
same text can be found (amongst others) running down in col A in Sheet2,
then we could try a VLOOKUP to replace the link formulas

Put instead in C2: =IF(A2="","",VLOOKUP(A2,Sheet2!A:G,7,0))
Copy down

Then Sheet2's sort would no longer impact/affect the values returned in
C2:C3

---
 
Hallo,
I have tried VLOOKUP and HLOOKUP, the datas are in the array in Sheet2
in col. C, so I was successful with
=VLOOKUP(B3;Sheet2!C4:G1000;7).
Thanks to your thoughts, which brought me to the right formula. BTW
what would you do with the IF command?
Cheers,
Juergen
 
Glad you got it working there !
what would you do with the IF command?
Click to expand...

The IF as used above (=IF(A2="","", ...) is just a simple error trap to
return a neat "blank" look in the formula cell should the lookup cell A2 be
empty/cleared. It should also help a little with calc performance, as
there's no need to calculate further the VLOOKUP if A2 is empty/cleared <g>.

---
 
Back
Top