+ Reply to Thread
Results 1 to 6 of 6

Formula to return dates into specific cell on another sheet

  1. #1
    Forum Contributor
    Join Date
    10-02-2013
    Location
    Houston, TX
    MS-Off Ver
    office 365
    Posts
    618

    Formula to return dates into specific cell on another sheet

    I have attached an example worksheet for your viewing.

    If you look at Sheet2 I would like to have a formula that looks at cells d3:l7 and if it finds it to have a number in that cell then it looks to the date above that column in (D1:L1) and places that date into Sheet1 under column c on the correct line.

    EX1: On DIR - Operational -- it has days 9/6 thru 9/9 checked on sheet 2
    I would like the formula to return 9/6, 9/7, 9/8, 9/9 into cell C3 on sheet1

    IF it could group the dates into a format like 9/6 - 9/9 it would be great I just need to show the dates of service in that column.

    Also sometimes we skip a couple days and then start up later in the same line so dates sometimes are would be 9/6 - 9/9, 9/15 - 9/28

    Can this be done
    Attached Files Attached Files

  2. #2
    Forum Expert azumi's Avatar
    Join Date
    12-10-2012
    Location
    YK, Indonesia
    MS-Off Ver
    Excel 2010
    Posts
    2,263

    Re: Formula to return dates into specific cell on another sheet

    Perhaps something like this? Please find file attach

    Thanks
    Attached Files Attached Files

  3. #3
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,639

    Re: Formula to return dates into specific cell on another sheet

    I think you need a VBA solution to achieve this task. Please find the attached sheet and follow the instructions (in Red) on Sheet1 to see if this is what you are trying to achieve.
    Attached Files Attached Files
    Last edited by sktneer; 09-10-2014 at 10:33 PM.
    Regards
    sktneer


    Treat people the way you want to be treated. Talk to people the way you want to be talked to.
    Respect is earned NOT given.

  4. #4
    Forum Contributor
    Join Date
    10-02-2013
    Location
    Houston, TX
    MS-Off Ver
    office 365
    Posts
    618

    Re: Formula to return dates into specific cell on another sheet

    Hey that is great I am on the right track. I attached my complete workbook and since you know how the smaller one was working this should be ok for you to understand better. I like the first formula how it groups it into one column.

    You can see the dates of service is on the Job Ticket sheet. But how do I extend this to the entire sheet on the Daily Chgs sheet to cover al the days?

    I can carry the formula down the sheet How do I adjust formula from the 9 days... to I think about 60 days on the sheet.

    Also you can see how some times you may skip a date with in certain time frame so how would that be represented in the answer. We would only usually use (9/5 -9/9 , 9/12-9/25)

    so how would it come out when a few days are left out

    Again thanks alot

    Thanks for your help
    Attached Files Attached Files

  5. #5
    Forum Contributor
    Join Date
    10-02-2013
    Location
    Houston, TX
    MS-Off Ver
    office 365
    Posts
    618

    Re: Formula to return dates into specific cell on another sheet

    Your solution is a little different than the other.... are you able to see the more complete worksheet to get a better feel how It looks and see if there would need to be any modifications for your VBA to work. I attached the worksheet titled "job ticket #3 v5 trial"

    Thanks for your help.

  6. #6
    Forum Contributor
    Join Date
    10-02-2013
    Location
    Houston, TX
    MS-Off Ver
    office 365
    Posts
    618

    Re: Formula to return dates into specific cell on another sheet

    I am not sure if you looked at the other sheet but I have entered that formula but the date ranges are coming in incorrect you can see the date ranges are 9/5-9/9 & 9/12

    The range coming in from formula is 9/5 - 9/1 how can I fix this?

    I am a little closer to getting it correct.
    I attached the trail B sheet that has your formula in it.

    Can you please direct my in the right direction.
    Attached Files Attached Files

+ 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. [SOLVED] Need formula to return a specific value if a specified date is between two other dates
    By BeachRock in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-04-2014, 02:03 PM
  2. Replies: 11
    Last Post: 11-30-2012, 08:04 PM
  3. Replies: 1
    Last Post: 08-07-2012, 03:07 PM
  4. Replies: 3
    Last Post: 06-20-2012, 07:16 AM
  5. Replies: 1
    Last Post: 06-05-2012, 02:22 PM

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