software

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
Agt;Z 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: www.excelforum.com/member.php...foamp;userid=5926
View this thread: www.excelforum.com/showthread...hreadid=504269Just some thoughts ..

gt; A.....12%.....Sheet2!G23
gt; 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=quot;quot;,quot;quot;,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
savefile.com/projects/236895
xdemechanik
---
quot;inigo45quot; gt; wrote in
message ...
gt;
gt; Hallo,
gt; How are the cell references maintained after sorting the referenced
gt; cells.
gt; Well, an example:
gt;
gt; In a workbook are two sheets, Sheet1 and Sheet2. Sheet1 is a ordinary
gt; listing with a subsequent cell reference to Sheet2, like
gt; A.....12%.....Sheet2!G23
gt; B.....33%.....Sheet2!G71
gt; ..
gt; Sum...100%.
gt;
gt; Sheet2 contains a continuously extended listing and should be sorted
gt; Agt;Z after new entries are added. I tried absolute and relative
gt; referencing, neither worked as it should. That is, if e.g. in the above
gt; example G23 moves to G14, it does NOT change in Sheet1! Absolute
gt; referencing is nonsense as you might suggest.
gt; Any ideas?
gt; Juergen
gt;
gt;
gt; --
gt; inigo45
gt; ------------------------------------------------------------------------
gt; inigo45's Profile:
www.excelforum.com/member.php...foamp;userid=5926
gt; View this thread: www.excelforum.com/showthread...hreadid=504269
gt;
Clarification:
The text in A2:A3 (in Sheet1) are also assumed unique in Sheet2's col A
--
Max
Singapore
savefile.com/projects/236895
xdemechanik
---

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: www.excelforum.com/member.php...foamp;userid=5926
View this thread: www.excelforum.com/showthread...hreadid=504269Glad you got it working there !

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

The IF as used above (=IF(A2=quot;quot;,quot;quot;, ...) is just a simple error trap to
return a neat quot;blankquot; 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 lt;ggt;.
--
Max
Singapore
savefile.com/projects/236895
xdemechanik
---
quot;inigo45quot; gt; wrote in
message ...
gt;
gt; Hallo,
gt; I have tried VLOOKUP and HLOOKUP, the datas are in the array in Sheet2
gt; in col. C, so I was successful with
gt; =VLOOKUP(B3;Sheet2!C4:G1000;7).
gt; Thanks to your thoughts, which brought me to the right formula. BTW
gt; Cheers,
gt; Juergen
gt;
gt;
gt; --
gt; inigo45
gt; ------------------------------------------------------------------------
gt; inigo45's Profile:
www.excelforum.com/member.php...foamp;userid=5926
gt; View this thread: www.excelforum.com/showthread...hreadid=504269
gt;