copy/paste loses links to other sheets?

AaronO

Hi all - I'm trying to replicate a piece of a plan within the same sheet. I'd like the duration of the tasks to be the same in both versions so if I change the duration in one version, the duration changes in the other version too - but it seems you can't link duration cells to each other.

What you can do, though, is link duration cells to another sheet. Fine, I have another sheet that has the expected durations of certain tasks; if I find out they'll take longer or whatever I can change them there and they are reflected back in the gantt.

But - if I copy a set of tasks and paste them within the same sheet, I lose the links to the external sheet in the pasted version. Is there a reason for that, or is it a bug or something else?

Anyone have a workaround? I can go in and manually relink the pasted cells to the source sheet but that's a pain.

thanks!

Seth Morth

Hi @AaronO!

Note that if you make the formula a Column formula Copy/Paste will not overwrite the formula.

Worst (Best?) case you can totally do this with formulas referencing cels between sheets rather than hard links, but riddle me this:

Is the Duration a fixed value, or the result of Start Date and End Dates? Can you simply copy the Start Date and End Date and create/drive the Duration? To that end, to replicate a Duration from Gantt to Gantt you need to link in the Start Date and End Date and the formula that creates the Duration...but that depends on what the hard data entry values are.

I have some examples of what I have done for formulas below. Note that the stuff in brackets {..} are references to other sheets and are created by clicking this link Reference Another Sheet when it appears during formula entry (you can prompt it to appear by typing in an '{' but you'll wind up with an extra '{' that you will need to remove)

CONTAINS...***THE EXAMPLE BELOW WORKS IF THE STOCK CODE FIELD Contains 'SCON01' as PART of the TEXT, BUT NOT THE WHOLE TEXT IN THAT CEL*** 

=IFERROR(SUMIFS({PO Detail_ReceivedValueInYear}, {PO Detail_StockCode}, CONTAINS("SCON01", @cell)), "")

COLLECT...***THIS IS A BUSY EXAMPLE, IT DOES A LOT; Sums up the $ total of any orders based on Supplier Name (exact match), and meets the specified month (1=January) of the current year*** 

=SUM(DISTINCT(COLLECT(OrderTotalCost:OrderTotalCost, SupplierName:SupplierName, ="Office Max", OrderEntryDate:OrderEntryDate, AND(MONTH(@cell) = 1, IFERROR(YEAR(@cell), 0) = YEAR(TODAY())))))

HAS...***THE EXAMPLE BELOW PULLS IN WHATEVER IS IN THE CEL, YOU CAN TOSS IN "AND" AND/OR "OR" TO HELP OUT; This one looks up a PO# and a PO Line# on Sheet-A and pulls in whether or not on Sheet-A how many have been received and displays it on Sheet-B (Where this formula lives)***

=IFERROR(INDEX(COLLECT({CGC-Purchase_Received}, {CGC-Purchase_PO}, HAS(@cell, [PurchaseOrder]@row), {}, HAS(@cell, [PO Line]@row)), 1), "Not Found")

• I hope this helps!