Cross Sheet References

klacayo

I need to have 2 columns update on a sheet to match when those same columns change on another (master) sheet. Is there a way to set that up without linking each individual cell? Those 2 columns are constantly adding new rows as new rows are added to the master sheet; I've set up those rows to copy from the master sheet via an automation, but from there I cannot figure out how to get the cells to update when they update on the master sheet. Help please?? Thanks!

Paul Newcome

Have you looked into creating a report?

Jeff Reisman

@klacayo Are you familiar with the INDEX/MATCH formula? This formula essentially says "give me the value from this column on the row where this value from my sheet matches the value in this other column." For this to work, you must have one fairly unique value in common between your two sheets and the master sheet.

For an example, if Sheet 1 and Sheet 2 both have a RowID column, and I want to pull the ReceivedDate value from Sheet 1 into Sheet 2, I would create the following INDEX/MATCH for that in Sheet 2:

=INDEX({Sheet 1 ReceivedDate column}, MATCH(RowID@row, {Sheet 1 RowID column}, 0))

When creating your formula, follow the onscreen prompts to reference another sheet in order to create your references to the columns in Sheet 1:

Select the sheet you want to retrieve values from, click on the header of the column you want, rename the range to something meaningful (optional, but I like to do it,) and then insert reference.