+ Reply to Thread
Results 1 to 9 of 9

Excel IF a cell value is within a date range, copy row and paste in another sheet

  1. #1
    Registered User
    Join Date
    02-10-2012
    Location
    Haymarket, Virginia
    MS-Off Ver
    Excel 2007
    Posts
    8

    Excel IF a cell value is within a date range, copy row and paste in another sheet

    I have a column of expiration dates and a column of corresponding items on a 'Data collection' sheet. I also have a 'Summary' sheet which I need to create a report for my boss. I need a way to list items on the summary sheet based on date ranges. What I am looking for is something that does "IF 'Data collection' G1 is <=TODAY()+30 THEN copy 'Data collection' H1 and paste into 'Summary' D1, IF NOT then don't do anything. I need to do this so I have a neat list of items based on less than or equal to 30 days, 30 to 60 days, 60 to 90, and more than 90.

  2. #2
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Excel IF a cell value is within a date range, copy row and paste in another sheet

    Why dont you attach some sample data?
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

  3. #3
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: Excel IF a cell value is within a date range, copy row and paste in another sheet

    One sheet of data & a Custom AutoFilter
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  4. #4
    Registered User
    Join Date
    02-10-2012
    Location
    Haymarket, Virginia
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Excel IF a cell value is within a date range, copy row and paste in another sheet

    Example.xlsx I tried making charts but I can't get the result I am looking for. Attached is an example of data that I am using, the correlating worksheets and another worksheet which shows what I am looking for as an end result. Unfortunately I can't send the original file. It is very large and has classified information on it. Thanks so much for the help!

  5. #5
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Excel IF a cell value is within a date range, copy row and paste in another sheet

    Your Expected output sheet doesnt match the calculations you require. For e.g. today minus 12/31/2011 gives 48 days. So ideally, 604 should appear in the 30-60 days column right?

  6. #6
    Registered User
    Join Date
    02-10-2012
    Location
    Haymarket, Virginia
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Excel IF a cell value is within a date range, copy row and paste in another sheet

    arlu1201- I'm pretty sure that the formula <=TODAY()+30 finds all dates 30 days ahead of the present date and all older dates. So the example should be correct

  7. #7
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Excel IF a cell value is within a date range, copy row and paste in another sheet

    I am not understanding the calculations. For e.g. today is 20th February (2/20/2012) minus 12/31/2011 gives 51 days. So this item 604 should belong to the 30 to 60 group right?

  8. #8
    Registered User
    Join Date
    02-10-2012
    Location
    Haymarket, Virginia
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Excel IF a cell value is within a date range, copy row and paste in another sheet

    Sorry, perhaps I didn't explain well. The less than 30, 30 to 60, and so on categories are for coming due dates. Anything that is 30 to 60 days out from today for instance would be in that category. Any date that is within 30 days from today or is in the past would be considered 30 days or less. For purposes of my project, these dates are conditionally formatted as red cells because they are either expired or are going to expire soon. Anyways, I still have not found a way to solve the issue of copying from that column and sorting into corresponding columns on the other sheet. I imagine I'm going to need some type of macro in order to accomplish due to the complexity, but I am a complete n00b when it comes to macros. Thanks for your interest in this, hopefully I can figure something out!

  9. #9
    Registered User
    Join Date
    02-10-2012
    Location
    Haymarket, Virginia
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Excel IF a cell value is within a date range, copy row and paste in another sheet

    So in the end, I just created a formula for pulling the data from Data Collection sheet over to specific columns in the Summary page based on date criteria. Then I created a simple macro to autosort using the record macro function. It works for what I need! Thanks everyone for the input.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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