Cell reference moves
 

I am usinng a spreadsheet with 2 sheets.
Sheet 1 contains a list of companies.
Sheet 2 refers to the company name on, sheet 1, by "=Sheet2!B2" type formular.
As I add names to sheet 1 I wish to re-order them alphabetically, however,
when I do this their relative position is not remeberd and I get the same
cell, but a different name. I need to retain the contents location.
Can you help please.

Cell reference moves
 

Hi,

Here's one way:
In sheet1 insert a blank column in front of the company, use thta column for
numbering your entries, when sorting select both columns and sort using the
company column, you can hide the added column when done, now on sheet2 use
something like this:

=VLOOKUP(ROW(A1),Sheet1!$A$1:$B$1000,2,0) and copy down as far as needed

A1 being the row of the first entry on sheet1 and $A$1:$B$1000 is the range
with your entries on sheet1, adjust accordingly!

HTH
Jean-Guy


"Big Geoff" wrote:

I am usinng a spreadsheet with 2 sheets.
Sheet 1 contains a list of companies.
Sheet 2 refers to the company name on, sheet 1, by "=Sheet2!B2" type formular.
As I add names to sheet 1 I wish to re-order them alphabetically, however,
when I do this their relative position is not remeberd and I get the same
cell, but a different name. I need to retain the contents location.
Can you help please.