Linking and locking cells across worksheets (rows)

Originally Posted by edmdas

Hi,

I think I have a VBA question, but there maybe a workbook function for this?
I have several worksheets in the same workbook, the first has a list of names (students) and data with formulas in rows alongside (Coloumn B cell 4 down to 173).

Other worksheets contain other data for the same set of names which they get by linking to the first worksheet 'Name' coloumn / cells.

If I sort the first sheet data based on names the other sheets change, but names only, the data on the other worksheets gets left in the original cell.

Is there away of linking cells in rows to the first cell in each row so if it moves they follow?

Thanks for any tips on direction, got a nagging feeling that this is gonna be simple.

Dave
(Slowly learning VBA)

Hi,

the 'norm' for this would be a VLookup or Match etc, to locate the row containing the required item (Name) thus, with a Name in column A of both sheets, and Sheet1 has now been sorted, and you want column D pertaining to the name for this row (row 5),

=VLookup(A5,Sheet1!A:D,4,False)

and for column B

=VLookup(A5,Sheet1!A:B,2,False)


hth
---

Originally Posted by edmdas

Thanks for reply, but I don't think I've made it clear as I am not looking for specific data results, just trying to keep data together across worksheets based on changes to one of the sheets. I’ll try to re explain and upload a sample.

I have sheet one, column B which has a list of names.

In adjacent columns C onwards is data (test results) for each name.

On Sheet two column B I have the same set of names by using the '= REF' formula and then in the columns C onwards more new data, other test results.

I have had to edit the names on sheet one column B, moving them or sorting them alphabetically.

The problem is that sheet two column B changes as it has the '=ref' to sheet one, but the other columns don't follow the move. The data on sheet two column C onwards gets left in the original row and so messed up.

Can I group the rows so they automatically move in sheet 2 as sheet one changes?

Thanks

Hi,

it is possible, but it wouldn't be done.

if you setup sheets to =Ref then you really need to ensure that the ref is not going to move.

As you discover, if you move the ref then you distort the data.

My suggestion would be that, before the sort on Sheet1, then on Sheet2 you Copy B4:B~whereever and Paste Special = Values back over itsself, the figures relate to the name currently showing, therefore set it to retain that name. Also, be cautious about using the ''=ref' in future, it may save you typing a name, but can produce unexpected results.

hth
---

Originally Posted by edmdas

Thanks

lesson learnt and a lot of redesigning to come

Oh hum

Dave

yes, we live and learn, but we rarely learn from the mistakes of others. Good luck with your designs, you can at least retain the current data with the Paste Special = Values.

Thanks for the response.

---