Cell Reference

[pa425804]

I have three worksheets, Sheet1, Sheet2 and Sheet3.

Sheet 1 contains the following data:
1 10
2 9
3 8
4 7
5 6

Sheet 2 contains the following data:
11 20
12 19
13 18
14 17
15 16

Sheet 3 contains the following formulae:
Sheet1!A1 Sheet1!B1
Sheet1!A2 Sheet1!B2
Sheet2!A1 Sheet2!B1
Sheet2!A4 Sheet2!B4

When I change the sorting in Sheet1 or Sheet 2, the cell of Sheet3 won't
update the "reference" to the "new location", i.e. A1 of Sheet 3 still refers
to Sheet1!A1, but in fact, the previous Sheet1!A1 could be Sheet1!A3 after
the sorting. Is there any way(s) to get around this problem? Many thanks.

 

[Spiky]

I have three worksheets, Sheet1, Sheet2 and Sheet3.

Sheet 1 contains the following data:
1 10
2 9
3 8
4 7
5 6

Sheet 2 contains the following data:
11 20
12 19
13 18
14 17
15 16

Sheet 3 contains the following formulae:
Sheet1!A1 Sheet1!B1
Sheet1!A2 Sheet1!B2
Sheet2!A1 Sheet2!B1
Sheet2!A4 Sheet2!B4

When I change the sorting in Sheet1 or Sheet 2, the cell of Sheet3 won't
update the "reference" to the "new location", i.e. A1 of Sheet 3 still refers
to Sheet1!A1, but in fact, the previous Sheet1!A1 could be Sheet1!A3 after
the sorting. Is there any way(s) to get around this problem? Many thanks.

Most would not consider this a problem, that is why Excel works this
way. What are you trying to do? I'm guessing your actual sheets aren't
quite this simple.