+ Reply to Thread
Results 1 to 9 of 9

Copy value from one sheet to another based on condition

  1. #1
    Registered User
    Join Date
    12-21-2017
    Location
    Amsterdam, Netherlands
    MS-Off Ver
    2010
    Posts
    12

    Copy value from one sheet to another based on condition

    In Excel 2010 I am struggling to get the correct formula and really hope that any of you can help me. Please.

    In sheet January I have values like below:
    Week # Planned load. start Plan.load.start time Sales Document sold to party
    1 02-01-2018 12:55 799999 123456
    2 09-01-2018 9:01 799900 972585

    Based on the week # I like the values to be copied to a sheet called wk01. I use the following formula:
    =if(January!$A19=1;VLOOKUP(January!$A19;January!$A$19:$P$251;2;FALSE);"Empty"). If I use the same formula in sheet wk02 I end up with cells mentioning Empty because I copy and paste the formula to the sheet wk02. This to remain flexible.

    I am looking for a formula that will copy the value from sheet January to wk01 (or wk02) but only if the value exists. Does such a formula exist?

    Thanks a lot in advance for your help.
    Marion

  2. #2
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: 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?

  3. #3
    Registered User
    Join Date
    12-21-2017
    Location
    Amsterdam, Netherlands
    MS-Off Ver
    2010
    Posts
    12

    Re: Copy value from one sheet to another based on condition

    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.
    Last edited by AliGW; 12-22-2017 at 04:19 AM. Reason: Unnecessary quotation removed.

  4. #4
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Copy value from one sheet to another based on condition

    Quote Originally Posted by Marion59 View Post
    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.
    Last edited by Richard Buttrey; 12-22-2017 at 05:26 AM.

  5. #5
    Registered User
    Join Date
    12-21-2017
    Location
    Amsterdam, Netherlands
    MS-Off Ver
    2010
    Posts
    12

    Re: Copy value from one sheet to another based on condition

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

  6. #6
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Copy value from one sheet to another based on condition

    Quote Originally Posted by Marion59 View Post
    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.

  7. #7
    Registered User
    Join Date
    12-21-2017
    Location
    Amsterdam, Netherlands
    MS-Off Ver
    2010
    Posts
    12

    Re: Copy value from one sheet to another based on condition

    Based on the week to week data and month to month data a graphic is made. That is why I am looking for this formula. Thanks in advance for any assistance.

  8. #8
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    14,270

    Re: Copy value from one sheet to another based on condition

    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: copy to clipboard
    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.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  9. #9
    Registered User
    Join Date
    12-21-2017
    Location
    Amsterdam, Netherlands
    MS-Off Ver
    2010
    Posts
    12

    Re: Copy value from one sheet to another based on condition

    Thank you very much for your help. Based on the earlier replies I have been able to change the document to show only three sheets. One were the data is entered; one that cumulates the data per month and per week and one that shows all the data per month and week in graphics.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Select row based on condition and copy to other different sheet.
    By Allard6 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-17-2016, 04:58 AM
  2. [SOLVED] Copy and paste (values) to another sheet based on condition
    By KK1234 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 10-09-2013, 10:25 PM
  3. Need a macro to copy data from one sheet to another based on condition.
    By kishoremcp in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-21-2013, 01:56 AM
  4. [SOLVED] Copy Data Range to another sheet based on a condition.
    By psunursingguy21 in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 01-30-2013, 03:50 PM
  5. Copy row to another sheet based on a condition
    By ltf09a in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-04-2012, 11:55 PM
  6. Copy rows from a master sheet based on condition
    By Rohun Heesen in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-13-2011, 08:33 PM
  7. Copy/Paste from one sheet to another based on condition
    By TheTempest in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 06-16-2010, 01:13 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1