Sorting list of names on one sheet updates information on other sheets

[danb1985]

Hi all,

I'm not sure if this can actually be done, but I was hoping there might be an Excel wizard out there who is able to point me in the right direction. A basic example of my source data is attached.

I have a spreadsheet with a worksheet called 'Names' that contains a list of up to 500 surnames/firstnames in Col A and Col B.

There are multiple other tabs covering different categories of information attached to these names that simply draw this information directly from the original cells (ie. =Names!A2, =Names!B2 etc.). This will go down to =Names!A500. At present, a lot of these cells are empty 'slots', waiting to be filled.

The tab called Names exists to avoid colleagues having to enter the same name once on each sheet - it just seems redundant.

However, if I add a new entry to the Names sheet, I would like to be able to sort this list alphabetically. If I do this normally, it will obviously update the order of names on all the other sheets. However, it will not update the information linked to those names, such as addresses or dates.

In the attached example, if I sorted the Names sheet, I would want the 'Category 1' sheet to display the surnames in order or Baker, Ball, Jones, Smith - but the three date columns would also need to behave as if they were sorted.

Is this even possible to do with VBA? I am guessing it would be a similar challenge if I wanted to remove a name - is there a way for it to update the rows on other sheets?

Thanks in advance - even if it's just to tell me that this can't be done with Excel!

[Kaper]

If you want it simple - why not introduce internal ID number. Then in other sheets instead of writing references to particular cells just write ID and use Vlookup.
See attachment for Cat1
if you want people here to be sorted by surnane select here all data A1:F5 and do sort (with sort dialog)

[danb1985]

Hi Kaper,

Thanks for the reply.

I'm not 100% clear on how this makes things any easier - are you saying that instead of sorting by Surname on the Names sheet, I would now sort by surname on the Cat1 sheet? Doesn't this still mean that, if I sort the 'Names' tab, the dates on Cat1 won't move and will therefore be assigned to the wrong person? I'm trying to help our end users by only needing them to sort one worksheet (Names) instead of the twenty (or more) separate sheets we have in our final spreadsheet.

I don't know if this could even be done with a macro as I am effectively trying to get it to sort multiple separate sheets by Column B, but the contents of each sheet are referencing the Names tab to ensure we have a consistent list of names.

Does that make sense? I do appreciate the help though!

[Kaper]

Not exactly - you can now sort each sheet independently (all can be of course sorted the same way) and the real benefit of such a solution is that it keeps consistency of data in all sheets.
(so here the following is false: "if I sort the 'Names' tab, the dates on Cat1 won't move and will therefore be assigned to the wrong person?" because in the proposed approach it will remain assigned to right person.)

[danb1985]

Hi Kaper,

Many thanks for your patience in explaining this to me. I think perhaps I have explained my problem poorly.

The overall goal for this is to have a single sheet that only needs sorting once (Names), and this feeds through to 20+ other sheets, so all sheets have identical name lists without the need to sort them separately. Sorting the sheets separately is exactly what we are trying to avoid!

Is that achievable at all?

[Kahalitao]

Kaper's suggestion is what you should look into... the idea is to have an 'ID' number associated with each line.
To update information on any other sheet, it would reference the ID number. This would allow the first sheet to be sorted and manipulated and adjusted numerous times, and still be able to maintain the data in the additional sheets.