Urgent Help Needed copy and pasting Values only when a condition is met

[Iank95]

Hi Everyone, 

I have an excel table that tracks the % of items that are in progress for a team. This is live data so when the dataset changes, so does the table. I want to be able to copy and paste these values every Friday so that I can see week on week trends. 

I was hoping i could say =if(today()=C3,C10,""). (in the screenshot this is how I would populate cell C4)This logic works but I would like the value to be static so that next week when the values in the source table have changed, the values in the tracker table have not. Is there a "paste values" version of this formula?

In the screenshot attached the first table is the table that I would like to populate on a week on week basis with the static values. The table below is the live table that I pull the data from. 

Thanks for the help

Ian 

[Iank95]

I have an excel table that tracks the % of items that are in progress for a team. This is live data so when the dataset changes, so does the table. I want to be able to copy and paste these values every Friday so that I can see week on week trends. 

I was hoping i could say =if(today()=C3,C10,""). (in the screenshot this is how I would populate cell C4)This logic works but I would like the value to be static so that next week when the values in the source table have changed, the values in the tracker table have not. Is there a "paste values" version of this formula?

In the screenshot attached the first table is the table that I would like to populate on a week on week basis with the static values. The table below is the live table that I pull the data from. 

Thanks for the help

Ian 

[#3]

@Iank95 

It seems you answered your own question. "Paste Values" is the only answer, unless you want a macro to auto paste the results for you.

Since your formula is taking the TODAY function, when the date doesn't equal any of the provided dates in row 3, the result will be blank.

[#4]

@adversi 

Hi thanks for your response. 

This doesn't do what I need because it will always be referencing that cell. So as the date changes it will appear blank. Once the date criteria is fulfilled I want it to paste the value so that it is then static and will not change so I can see the trends on a week on week basis. Do you know how I can do this? Is there a paste values formula? I can't seem to find one. 

Thanks

Ian

[#5]

@Iank95 

From the picture I can not recognize or understand your plan (maybe also the translation).

Since the possibilities in Excel are very diverse, the possible solutions are also diverse.

Therefore, with your permission, please download a file (without sensitive data) with your request.

At the same time, I would ask you to name the Excel version and the operating system, as there may be different approaches to the proposed solution.

Thank you for your understanding and patience

Nikolino

I know I don't know anything (Socrates)

[#6]

@Iank95 

Basically, to replace a formula by a constant value requires human action or a macro.

There are tricks that give the result you require but they could cause problems using the workbook.  If you allow circular references with a single step then

= IF( (date=today)*(thisCell=0), value, thisCell)

will copy the value into 'thisCell' provided it is initially blank and the dates match.  Otherwise it will reference itself and retain its existing value.  If you try it, I would be interested to know how you get on!  Maybe I am being unduly pessimistic.

[#7]

@NikolinoDE 

Hi Nikolino, 

Thank you for your response, I will try to be clearer. 

In the first screenshot, this is my source data table. This data flow has a connection to a SharePoint list so it is constantly changing (I have greatly simplified the data set).

The second screenshot is a summary pivot table that shows me how many projects are "In Progress" or "Not In Progress". This table is also continuously changing as the source data changes. 

The final screenshot is the table that I am trying to populate. What I want to do is copy the data from the summary pivot table (2nd screenshot) every Friday (When the date condition is met) but I do not want that data entry to change as the source data changes. This will allow me to populate that table over time and see the trend on a week on week basis. 

Please let me know if you can help as I am really stuck. 

Thanks in advance

Ian

[#8]

@Peter Bartholomew 

Hi Peter, 

Unfortunately, this does not work. I would be very grateful if you could review my response to Nikolito as I have tried to be as clear as possible in my response. 

Thank you in advance

Ian

[#9]

@Iank95 

As far as I could understand (... thanks to the translation, or maybe not) you want to copy certain areas from a workbook / worksheet to a new workbook / worksheet.

If this is the case, then maybe this VBA code could help you, you just have to set it up according to your ideas and let it run with a button.
It's just an idea.

Sub TestCopypaste ()
     Range ("A2: L2"). Select
     Selection.Copy
     Windows ("target mappe.xls"). Activate
     ActiveSheet.Paste
     Windows ("Data mappe.xls"). Activate
     Range ("A5: L5"). Select
     Application.CutCopyMode = False
     Selection.Copy
     Windows ("target mappe.xls"). Activate
     Sheets ("Table2"). Select
     ActiveSheet.Paste
     Windows ("Data mappe.xls"). Activate
     Range ("A8: L8"). Select
     Application.CutCopyMode = False
     Selection.Copy
     Windows ("target mappe.xls"). Activate
     Sheets ("Table3"). Select
     Range ("A2"). Select
     ActiveSheet.Paste
End Sub

Thank you for your understanding and patience

Nikolino

I know I don't know anything (Socrates)

[#10]

@Iank95 

There are some undesirable characteristics but it should work.

Firstly the final table should not use text for the date series (not an Excel Table).

The workbook must allow circular referencing.

To clear values once set the formula has to be cleared and re-entered by copying across from an adjacent cell using Ctrl/Enter.

If you set out to use VBA try to limit the transfer to reading and writing the cell values rather than copy/pasting all the formatting properties along with the value.