+ Reply to Thread
Results 1 to 4 of 4

Project Plan / Timeline - lookup date by searching 'x' marker

  1. #1
    Registered User
    Join Date
    09-22-2009
    Location
    Mumbai India
    MS-Off Ver
    Excel 2007 & 2010
    Posts
    77

    Project Plan / Timeline - lookup date by searching 'x' marker

    Hi all, I am building an MS Project plan and decided to start from an Excel template so I can send it to each resource to complete themselves. I have formatted the sheet so wherever you place an 'x' the cell is coloured.

    I now want to map the start date of the task and end date of the task by searching the row for the first 'x' and looking up to the date at the top. I also want to find the last x in the row and look up the date to give the finish date of the task.

    Can someone please help me? I have tried variations of find, search and hlookup but don't know how to find the first or last 'x' in a row and then show the date. I tried an IF function too but it became unworkable.
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    09-21-2011
    Location
    Birmingham UK
    MS-Off Ver
    Excel 2003/7/10
    Posts
    2,188

    Re: Project Plan / Timeline - lookup date by searching 'x' marker

    in column c

    =INDEX($A$5:$AB$5,1,MAX(IF($D6:$AB6="x",COLUMN($D6:$AB6))))


    you need to press CTRL + SHIFT + ENTER to commit this, you can drag it down for the other cells.
    Hope this helps

    Sometimes its best to start at the beginning and learn VBA & Excel.

    Please dont ask me to do your work for you, I learnt from Reading books, Recording, F1 and Google and like having all of this knowledge in my head for the next time i wish to do it, or wish to tweak it.
    Available for remote consultancy work PM me

  3. #3
    Registered User
    Join Date
    09-22-2009
    Location
    Mumbai India
    MS-Off Ver
    Excel 2007 & 2010
    Posts
    77

    Re: Project Plan / Timeline - lookup date by searching 'x' marker

    Very good thank you! Took a minute to figure out but it works very well. I shall try and copy the structure for column b. Thanks for your swift response.

  4. #4
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Project Plan / Timeline - lookup date by searching 'x' marker

    Try these...

    For the start date...

    Entered in B6:

    =IFERROR(INDEX(D$5:AB$5,MATCH("x",D6:AB6,0)),"")

    Format as Date

    For the end date...

    Entered in C6:

    =IF(COUNTIF(D6:AB6,"x")<2,"",INDEX(D$5:AB$5,MATCH("xx",D6:AB6)))

    Format as Date

    That double xx is intentional!

    Select B6:C6 and copy down as needed.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

+ 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] Project Timeline Tracker
    By AnnaG87 in forum Excel General
    Replies: 1
    Last Post: 04-30-2013, 11:39 PM
  2. [SOLVED] Project Timeline with Dynamic Date Range Based on User Input
    By Rollinstone12 in forum Excel General
    Replies: 2
    Last Post: 10-28-2012, 10:07 AM
  3. Does anyone have an Excel template for a project plan?
    By Kynthia Melissa in forum Excel General
    Replies: 1
    Last Post: 07-17-2012, 04:17 PM
  4. Project Plan Tracking
    By kirps in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-12-2012, 12:17 AM
  5. Project timeline calculation
    By COGICPENNY in forum Excel General
    Replies: 5
    Last Post: 07-04-2012, 03:33 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