Copy value from one sheet to another based on condition

If your data is in the right layout then an Advanced Data filter would be preferable, or probably better still why not use a Pivot Table and have complete flexibility?

Thank you for answering my question. The reason I want to do it as discribed is because management wants to see what happened in week 1, week 2, etc.

Originally Posted by Marion59

Thank you for answering my question. The reason I want to do it as discribed is because management wants to see what happened in week 1, week 2, etc.

...but that's precsisely what a pivot table will allow you to do, along of course with lots more stuff. As a general rule avoid duplicating database fields which are the same data but just in a different format. A week is a series of dates, there really is no necessity to have a week number column.

Management most probably don't understand what Excel Pivot Tables can do for them. In my working life I was constantly surprised at that lack of awareness. I suggest you educate them. If you take a little time to study Pivot Tables you'll certainly learn something that will stand you in good stead and if your organisation has other very basic Excel applications you might even become the Goto source for all matters Excel.

Originally Posted by Marion59

Management wants to see the figures from week to week and that is why I want to copy data from one sheet to another.

Management can't observe 52 sheets at one and the same time. They presumably want to see a particular week and then after that see another week. All of which can be done by either Grouping the Pivot Table by week, or by filtering a specific week.

Or if you don't want to use a Pivot Table then use an Advanced Filiter and a criteria variable cell to extract the records for any particular week to a template sheet. What's important is to be able to see the records for a particular week. Where those records exist at the time they are seem really doesn't matter.

A pivot table would be a good way to go, however I also understand having to deal with management.
I believe that the following array entered formula* will do what you want:

Formula:

Please Login or Register  to view this content.

I have applied the formula to what I believe is the data layout described in post #1. If the layout is not correct, and the formula doesn't work with the actual layout, please upload a sample spreadsheet, that includes manually input results (which we can then attempt to automate), by clicking on the GO ADVANCED button below the Quick Reply window and then scrolling down to Manage Attachments to open the upload window.
*Array entered formulas are confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
Let us know if you have any questions.