How can I update data in one sheet from another sheet?

Malary Neri

I have a New Hire Master sheet to keep track of our New Hire's onboarding tasks. The first eight columns contain data that needs to be pulled from the New Hire Master Sheet over to the Membership Tracker sheet.

I'd like to use a formula to update the the 8 columns in the Membership Tracker with the latest data that is entered in these same 8 columns on the New Hire Checklist - MASTER.

I DO NOT want to copy the data from one sheet to another.

I have included example screenshots to help get a better idea.

Any ideas or thoughts? Thank you in advance!

Hollie Green

If you have a unique column that is common to both sheets you can use a =Index(Collect formula to pull in the data. If you don't you can set up an automatic row numbering column to give you a unique value.

Paul Newcome

Is this just for listing purposes, or are you planning to also manually update other columns with new data on the second sheet?

Malary Neri

I am planning on manually updating the other columns with new data on the 2nd sheet.

Paul Newcome

If you have a unique identifier on every row, you can use @Hollie Green's suggestion of an INDEX/MATCH or INDEX/COLLECT.

However, using the INDEX/MATCH or INDEX/COLLECT method without a unique identifier on each row to pull the 8 columns over is very inadvisable. The short version is that if the data moves (sorting / deleting / adding rows) on the source sheet, the formulas will also pull in the new order, but it does not actually move the rows in the second sheet. This means manually entered data on row 5 is always on row 5 even if the formula on row 5 starts pulling in a different row from the source.

In that case, my suggestion would be to have all of the columns on the one sheet and then use report(s) to show which columns you would want on the two differnet "sheets".