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

Dell XPS 13 9370 (2018) review

Creative studio Omnibus' brand identity for We Said Enough fights back against sexual misconduct

WWDC history: Apple's product launches since 2005

Espace de stockage : comment libérer de la mémoire sur votre iPhone ?