Link gropu of cells so that they will move with / follow the first cell in the group

[fungo4]

Hi
I am a complete novice to Excel programming but do have past self-taught VBA programming experience when I created a relatively-simple data base application in Access many years ago.

I am assuming that the solution to my problem, as listed below, requires a macro or VBA programming but I'm not certain of this.

I have a column of items. Subsequent columns are characteristics that each item can be scored/rated on. Each item may be scored/rated on 30 or more characteristics, the scores being listed in the subsequent cells in the same row as the item itself.

The items themselves, which are in the first column, are drawn-from / linked-to a list of the items on a different sheet (permits me to edit the items list in one location as I use the items for different purposes on other sheets).
I an looking for a way that if I change the order of the items list on the source sheet, that the items on the characteristics sheet will be re-ordered in the exact same way AND that all of the item's characteristics scores/ratings, which appear in the subsequent cells in the same row as the item, will automatically move to the new row that the item has been moved to (thereby keeping the scores/ratings associated with the correct item).

Thanks!

[Glenn Kennedy]

Hi there.

A picture is worth 1,000 words. An Excel sheet is worth 1,000 non-editable pictures.

Please read the yellow banner about sample worksheets, at the top of the screen. Act on its guidelines and post a SMALL sample sheet complete with an explanation and some expected results.

[fungo4]

Sample.xlsx

I hope I have succeeded in attaching the spreadsheet.

If so, let's say that I wanted to switch the order of the items such that item5 comes before item4 in the list. If I switch the order on sheet1 the order of the items switches appropriately on sheet 3 since they are linked but I would like the characteristic scores associated with each item to switch along with the items

Thanks

[HansDouwe]

Please try in A2

Please Login or Register  to view this content.

and in A8

Please Login or Register  to view this content.

[fungo4]

Hi.
Thanks for the help and sorry for my delayed response (I hadn't had a chance to look it over until today).
You suggestion is close but doesn't quite do what I want. First of all, as best as I can tell, the behavior seems to be strangely-unpredictable.
As I understand what you did:
Sheet 2 A2 to A6 automatically takes sheet 1 A2 to A6's values and puts them in ascending alphabetic order. Columns B through G are stay as they were since the order of A2 to A6 never changes, even if I change the order on sheet 2.
Sheet 2 A8 to A12 takes the data from the array above it on the same sheet (columns A to G, rows 2 to 6) but keeps them in the same order as they appear on sheet 1 thus when I change the order of the items on sheet 1, the order on sheet 2 for rows 8 to 12 should change to match it.

The behavior of the top array on sheet 2 isn't exactly what I need since there may be instances where I want to change the name of one or more of the items. If this changes the alphabetic order of the items then the scores don't stay aligned with the item in question.

Also, the lower array on sheet 2 does not seem to be functioning exactly as described/intended. For starters, when I opened the sample file that you sent, before I even changed anything, the order of the items in sheet 2 rows 8 to 12 was different from the order in sheet 1. If I then sort A2 to A6 on sheet 1 in alphabetic order (either ascending or descending), indeed the array in rows 2 to 6 on sheet 2 does not change and that in rows 8 to 12 rearranges itself to match the order on sheet 1, be it ascending or descending. If, however, I manually swap cells A2 and A3 on sheet 1 when they are in descending order, something strange happens on sheet 2. Instead of showing up in the order item4, item5, item3, item2, item 1 as they are on sheet 1, the order on sheet 2 changes to item5, item4, item3, item1, item2.

Any ideas?

Thanks much!