Excel cell references

  Newuser939 12:32 02 Apr 2010

I have a workbook in which Sheet 1 contains several rows and columns of data. On Sheet 2 one column of data is the same as one of the columns on Sheet1. Accordingly, to avoid entering the same data twice I highlight the cell in Sheet 2, insert =, highlight the equivalent cell on Sheet 1 and press enter. So far so good. However, if I then sort my rows on Sheet 1 into a different order (eg alphabetical), Sheet 2 does not pick up that change so that the data on Sheet 2 is then wrong. Is there any way of persuading Sheet 2 to reflect changes in the order of data on sheet 1? Apologies if I haven't explained it very well.

  VoG II 12:47 02 Apr 2010

That doesn't happen for me.

Try this: in A1 of Sheet2 enter the formula


and copy down as far as needed. Changes in Sheet2 should now be mirrored in Sheet2.

  VoG II 12:48 02 Apr 2010

Final sentence should be

Changes in Sheet1 should now be mirrored in Sheet2.

  Newuser939 17:04 05 Apr 2010

Thank you so much for your reply. I am sorry for the delay, but I have since been fiddling about trying without much success to make this aspect of Excel work as I thought it should. Would it be too much of a cheek to email you a very small sample spreadsheet (with fictional data, of course) to show better the problem I have encountered?

  VoG II 17:10 05 Apr 2010

Try uploading your example to a file sharing site like click here and providing a link in this thread.

  Newuser939 17:26 05 Apr 2010

I haven't used file sharing before, but all being well, the sample file is here
click here
What I find is that if, for example I sort the data on Sheet 1 so that the names are in alphabetical order, the prices on Sheet 2 are no longer correct. Is it something I am doing wrong?

  VoG II 17:39 05 Apr 2010

You didn't have formulas in Sheet2 referring to Sheet1. I have added them and Sheet2 changes to reflect the contents of Sheet1.

click here

  Newuser939 18:32 05 Apr 2010

Thank you very much for taking time on this.
I think that on my version, the formulas on sheet 2 were present in column 3, but only column 3. However, it is beginning to look as if it may be a problem of some sort with my computer. I say that because your amended version does not work properly here. For example, if I data sort sheet 1 by column c (ie price) I finish up with wrong prices on Sheet 2, where, incidentally 2 vegetables have been added from page 1 which were not there in the original. I may have to give up.

  VoG II 18:50 05 Apr 2010

If you want the whole of Sheet2 to always mirror what is in Sheet1 You'll need to add formulas to all of the columns, e.g.

Sheet2 A3 =Sheet1!A3

then copy the formula across and down.

  Newuser939 14:17 06 Apr 2010

Thank you for that. At long last I understand the principles involved.

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Sea of Thieves Review

Dell Canvas review: the cheap Wacom Cintiq alternative

How to use iMovie for Mac, tips and more

Comment filmer l’écran d’un iPhone ?