Copy and paste a linked cell value to another row

AMarie91

I have several cells that are linked to dates or various values in another sheet. I then want to copy and past these linked cells to track weekly updates. Is it possible to do this? When I try to paste special the "link to copied cells" is grayed out. Is there another formula I can use that will stay linked if I copy and paste ??

Michelle Choate 2

There is a formula you can use to pull in the dates from one sheet to another and link them. So you update them in the source sheet and then the destination sheet would have a formula like INDEX/MATCH or INDEX/COLLECT to pull the information into the destination sheet.

INDEX/MATCH = One criteria.

INDEX/COLLECT = Multiple criterions. Most often you will use COLLECT.

Essentially an INDEX/COLLECT is a way to bring back one value based on multiple criteria.

The COLLECT function acts like a filter, so you list all of your columns and criteria in a row, and at the very front you have the column to bring a value back from.

For example:

=INDEX(COLLECT({Column To Return}, {Criteria Column 1}, "Criteria 1", {Criteria Column 2}, "Criteria 2"), 1)

You need the 1 at the end of the INDEX function to identify what row to bring back. In this instance, the first match for all those criteria.

If you may have multiple matches for the same criteria, you can use JOIN/COLLECT

=JOIN(COLLECT({Column To Return}, {Criteria Column 1}, "Criteria 1", {Criteria Column 2}, "Criteria 2"), ", ")

Then this will bring your multiple values together in one cell.

If you want to walk thru how to do this, feel free to email me. michelle.choate@outlook.com