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, 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?
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, 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.