Hi. I have worksheet A and worksheet B. Worksheet A has several columns
that can be sorted in any order. Worksheet B also has several columns that
can be sorted in any order. My problem is that I need to copy the content of
certain cells from worksheet B to worksheet A, but, as B is capable of being
re-sorted then that means the source cell reference changes and thus I'm left
with 'blanks' (or zeroes) in the worksheet A cell destination. I can see
that the problem would go away if I made the column list in worksheet B
'static', but that is not possible as both worksheets are designed to be
sorted. I've tried all the usual things between the source cells and the
destination cells such as paste link, paste special, paste hyperlinks but no
matter what I try, if worksheet B is re-sorted then I loose the pointer to
the data in worksheet A. Hope this makes sense.
--
Peter

I know you said you've tried the usual but have you tried using VLOOKUP to
match some unique marker cell in worksheet A to the same marker cell in
worksheet B? Then no matter how you sort either sheet, the markers move but
the VLOOKUP always finds the unique marker rather than a cell reference.
Example:

Worksheet B:
Column A Column B
Patrick 12
Peter 15
Sandra 19
Jill 8

Worksheet A:
Column A Column B
Peter =VLOOKUP(A1,SheetB!A:B,2,0)
Sandra =VLOOKUP(A2,SheetB!A:B,2,0)
Patrick =VLOOKUP(A3,SheetB!A:B,2,0)
Jill =VLOOKUP(A4,SheetB!A:B,2,0)

If this helps please click "Yes"
<<<<<<<<<<

"Peter" wrote:

Hi. I have worksheet A and worksheet B. Worksheet A has several columns
that can be sorted in any order. Worksheet B also has several columns that
can be sorted in any order. My problem is that I need to copy the content of
certain cells from worksheet B to worksheet A, but, as B is capable of being
re-sorted then that means the source cell reference changes and thus I'm left
with 'blanks' (or zeroes) in the worksheet A cell destination. I can see
that the problem would go away if I made the column list in worksheet B
'static', but that is not possible as both worksheets are designed to be
sorted. I've tried all the usual things between the source cells and the
destination cells such as paste link, paste special, paste hyperlinks but no
matter what I try, if worksheet B is re-sorted then I loose the pointer to
the data in worksheet A. Hope this makes sense.
--
Peter

Hi Rick - OMG it works!!! I've never used vlookup and am now a fan! I've
struggled with this for a couple of days and am sooooo grateful. I guess
with all things Excel it's about knowing how to ask the question - you don't
know what you don't know! If there is anything, from a User perspective, you
ever need any help with i.e testing out a theory, some data entry etc..
please pop me a note - I'd be only too happy to help. Cheers again.
--
Peter


"BSc Chem Eng Rick" wrote:

I know you said you've tried the usual but have you tried using VLOOKUP to
match some unique marker cell in worksheet A to the same marker cell in
worksheet B? Then no matter how you sort either sheet, the markers move but
the VLOOKUP always finds the unique marker rather than a cell reference.
Example:

Worksheet B:
Column A Column B
Patrick 12
Peter 15
Sandra 19
Jill 8

Worksheet A:
Column A Column B
Peter =VLOOKUP(A1,SheetB!A:B,2,0)
Sandra =VLOOKUP(A2,SheetB!A:B,2,0)
Patrick =VLOOKUP(A3,SheetB!A:B,2,0)
Jill =VLOOKUP(A4,SheetB!A:B,2,0)

If this helps please click "Yes"
<<<<<<<<<<

"Peter" wrote:

Hi. I have worksheet A and worksheet B. Worksheet A has several columns
that can be sorted in any order. Worksheet B also has several columns that
can be sorted in any order. My problem is that I need to copy the content of
certain cells from worksheet B to worksheet A, but, as B is capable of being
re-sorted then that means the source cell reference changes and thus I'm left
with 'blanks' (or zeroes) in the worksheet A cell destination. I can see
that the problem would go away if I made the column list in worksheet B
'static', but that is not possible as both worksheets are designed to be
sorted. I've tried all the usual things between the source cells and the
destination cells such as paste link, paste special, paste hyperlinks but no
matter what I try, if worksheet B is re-sorted then I loose the pointer to
the data in worksheet A. Hope this makes sense.
--
Peter