Copy/Paste Cell while retaining inbound link

katie.dickinson

TL;DR

I want to copy a cell from Sheet A (which has an inbound link from Sheet B) and paste into Sheet A while retaining the inbound link. Currently, when I copy the cell from Sheet A and paste into Sheet A, it only pastes the value of the cell and loses the inbound link.

______________________________________________________________________________________________

I have a sheet that my team uses as a checklist of weekly tasks - it's part of our controls to ensure weekly work is done and accountability should mistakes be made. Let's say there are 50 tasks, each with their own row. We perform the same 50 tasks each week. So at the end of each week, we copy the 50 rows and paste them so we can start over the next week. This happens in sheet we'll call Daily Checklist

One of the columns on each of these rows is a RYG ball which links IN from a different sheet (let's call it Procedure Sheet) and correlates to the status the Procedure (instructions on how to do the task) - that sheet has built in formulas to update the RYG ball to RYG depending on if the Procedure doesn't exist yet, is old, or is good to use.

The problem is that, when we copy the 50 rows in the Daily Checklist sheet, the links IN from the Procedure Sheet do not paste as well. This means I have to re-link each cell every week, if I want the Daily Checklist to accurately indicate the status of the Procedure.

Is there a way to copy a cell in the Daily Checklist sheet (which has a link IN from the Procedure Sheet) and paste that cell in the Daily Checklist sheet while retaining the inbound link?

katie.dickinson

I did try copy/pasting the values and it did break the reference. Your idea to use INDEX/MATCH was very helpful though - I didn't know how those formulas worked and I've been able to develop a workaround.

At the top of my sheet I created a "key" where I created inbound links from the cells from the other sheet. The key essentially had two columns - RYG ball for status and text for Procedure Name - the contents of which are sourced from the other sheet via a link.

I then added a column for Procedure Name on each of the task rows - that Procedure Name never changes and matches information in the key. Then I used =INDEX($[Procedure Status]$1:$[Procedure Status]$2, MATCH([Procedure Name]12, $[Primary Column]$1:$[Primary Column]$2, 0), 1) so that the task row accurately looks up the status based on the Key. Since the formula pastes without issue, this solved my problem.

L_123

The only thing I can think of is using a copy to workflow with a index match reference to the original sheet in it's own column.

Are you overwriting the information in the sheet you are copying the data to or adding it to the top or bottom?

katie.dickinson

Thanks for the response - I'm not overwriting any information on the sheet, just adding to the bottom of the existing data.

I think this might be a possible solution - my experience with formulas and copy workflows has been non-existent up until now though. I will check out some of the pages in the Learning Center to see if I can put it together. Then I'll check back in and let you know if it works. Thank you again!

katie.dickinson

Unfortunately, unless I'm using it wrong, it doesn't look like I can use workflow automation to copy rows into the same sheet, I'd have to move them to a new sheet. It did copy them over exactly as needed though.

L_123

I didn't realize you were using the same sheet. Did you try copy pasting the values? If you right click to paste, then select values instead of just pasting it should keep the same values that were at the top instead of breaking your reference. You could also take a look into absolute references as a way to keep your references from changing when moving/copying data.

katie.dickinson

I did try copy/pasting the values and it did break the reference. Your idea to use INDEX/MATCH was very helpful though - I didn't know how those formulas worked and I've been able to develop a workaround.

At the top of my sheet I created a "key" where I created inbound links from the cells from the other sheet. The key essentially had two columns - RYG ball for status and text for Procedure Name - the contents of which are sourced from the other sheet via a link.

I then added a column for Procedure Name on each of the task rows - that Procedure Name never changes and matches information in the key. Then I used =INDEX($[Procedure Status]$1:$[Procedure Status]$2, MATCH([Procedure Name]12, $[Primary Column]$1:$[Primary Column]$2, 0), 1) so that the task row accurately looks up the status based on the Key. Since the formula pastes without issue, this solved my problem.

katie.dickinson

I was going to post a picture of the workaround but keeps crashing my browser. Thank you for your help with the formula suggestion - this does what I need.