Links dont update when source data is sorted

Hi again,

I have set up my spreadsheet with 2 tabs. Tab 1 has the source information and Tab 2 has links to the appropriate cell ie Tab 1 (labelled as Ward 1 ACH Staff Trg) has a date and Tab 2 (which collects only relevant info from the data from the source sheet) shows that date (the link is in the format of ='Ward 1 ACH Staff Trg'!H97)

I now find I need to sort the source data listed in Ward 1 ACH Staff Trg.

I have identified the sort arrangement and have added a column at the far left of the sheet and populated that column with a number 1 against each row which needs to be sorted. This will bring all the "1" rows together at the top of the spreadsheet.

The problem is when this is sorted the link which is looking at H97 (='Ward 1 ACH Staff Trg'!H97) does not follow and update to the new position following the sort.

Is this correct? How do I ensure the link updates to reflect the new position of the data currently in H97?

Thanks for your help.

Originally Posted by Glenn Kennedy

Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).

1. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential information is removed first!!

4. Try to avoid using merged cells. They cause lots of problems!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Thanks for your offer of assistance.

I have reproduced parts of my spreadsheet and its attached.

The sort is based on Column A. Only some of the rows have a 1 against then as it is those rows I wish to bringng together at the top of the spreadsheet.

I have added 2 extra tabs to show what I want. That is, Tab 3 shows the data after sorting (based on column A) and Tab 4 shows how I want the data to map to.

I hope this makes sense to you.