maintain worksheet reference
 

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

In a workbook are two sheets, Sheet1 and Sheet2. Sheet1 is a ordinary
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 sorted
AZ after new entries are added. I tried absolute and relative
referencing, neither worked as it should. That is, if e.g. in the above
example G23 moves to G14, it does NOT change in Sheet1! Absolute
referencing is nonsense as you might suggest.
Any ideas?
Juergen


--
inigo45
------------------------------------------------------------------------
inigo45's Profile: http://www.excelforum.com/member.php...fo&userid=5926
View this thread: http://www.excelforum.com/showthread...hreadid=504269

maintain worksheet reference
 

Just some thoughts ..

A.....12%.....Sheet2!G23
B.....33%.....Sheet2!G71

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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"inigo45" wrote in
message ...

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

In a workbook are two sheets, Sheet1 and Sheet2. Sheet1 is a ordinary
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 sorted
AZ after new entries are added. I tried absolute and relative
referencing, neither worked as it should. That is, if e.g. in the above
example G23 moves to G14, it does NOT change in Sheet1! Absolute
referencing is nonsense as you might suggest.
Any ideas?
Juergen


--
inigo45
------------------------------------------------------------------------
inigo45's Profile:
http://www.excelforum.com/member.php...fo&userid=5926
View this thread: http://www.excelforum.com/showthread...hreadid=504269

maintain worksheet reference
 

Clarification:
The text in A2:A3 (in Sheet1) are also assumed unique in Sheet2's col A
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

maintain worksheet reference
 

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


--
inigo45
------------------------------------------------------------------------
inigo45's Profile: http://www.excelforum.com/member.php...fo&userid=5926
View this thread: http://www.excelforum.com/showthread...hreadid=504269

maintain worksheet reference
 

Glad you got it working there !

what would you do with the IF command?
.. =IF(A2="","",VLOOKUP(A2,Sheet2!A:G,7,0))

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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"inigo45" wrote in
message ...

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
Cheers,
Juergen


--
inigo45
------------------------------------------------------------------------
inigo45's Profile:
http://www.excelforum.com/member.php...fo&userid=5926
View this thread: http://www.excelforum.com/showthread...hreadid=504269