+ Reply to Thread
Results 1 to 5 of 5

Complex Formula for Auto-populating function

  1. #1
    Registered User
    Join Date
    08-01-2019
    Location
    London
    MS-Off Ver
    2016
    Posts
    31

    Complex Formula for Auto-populating function

    Hi all,

    First off, my apologies for the vague title, I appreciate it isn't very descriptive, but I'm afraid I don't really know how to summarise what I need help with in a short sentence.

    A couple of months ago, I set up a document that allows Project Managers to identify the next "Stage Gates" their various Projects were expected to hit, along with a function to indicate the respective BRAG status and planned date for reaching said gate - see "Current SG Tab" in the attached file.

    As you'll note from the document, the Project Name column is currently pulling the list Projects based on the indicated Programme in A4, and the remaining columns provide drop-down lists that Project Managers can then select from as required.

    I have since been asked to expand the function of this particular document, in which the desired outcome is that Project Managers indicate the BRAG status for all Stage Gates (past and future), which would require that the layout of the document be similar to that shown in "Ideal SG Tab".

    Unfortunately, I can't figure out how to automate the document to provide this particular layout, or anything similar to it. I would very much welcome any suggestions and ideas you may have on how to resolve this.

    Many thanks in advance, and I look forward to hearing from you.

    G
    Attached Files Attached Files

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    30,754

    Re: Complex Formula for Auto-populating function

    Grrrrr. I hate these "cut down" sheets where there are few/no rows for playing around on. I hate 'em, I hate 'em, I hate 'em....

    See if this is what you want. Select a project from the orange shaded DD boxes. If it's not what you want, please explain again what you do want... If it is what you want, then I can explain...
    Attached Files Attached Files
    Glenn



  3. #3
    Registered User
    Join Date
    08-01-2019
    Location
    London
    MS-Off Ver
    2016
    Posts
    31

    Re: Complex Formula for Auto-populating function

    Hi Glenn,

    First off, my apologies for the lack of cells, I can appreciate how annoying that may be to many users, though it's a preference of mine particularly for focusing on the cells to be used.

    Second, this works perfectly! Can I trouble you to explain the reasoning and method behind it?

    Regards,
    G

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    30,754

    Re: Complex Formula for Auto-populating function

    Each project has six stages. Then you want a gap (blank row). The orange cell (e.g. A8) has your DD in it, from which you select the individual project. B8-B13 has this:

    =IF([@[Project Name]]<>"",INDEX(Lists!$J$2:$J$7,1+MOD((ROWS(B$8:B8)-1),7)),"")

    red: If the project name is non-blank
    orange: from this range
    cyan: return value 1, 2, 3, as you copy down. This is simply a counter.
    Blue: If the project name is blank, return a blank.

    A9-A13 just copies the project name down, if there is one.

    To allow for more than one project, select A8 to B14. Copy.

    Select A15. Paste.

    Select A22. Paste.

    Done.


    You're welcome.



    If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

    It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

  5. #5
    Registered User
    Join Date
    08-01-2019
    Location
    London
    MS-Off Ver
    2016
    Posts
    31

    Re: Complex Formula for Auto-populating function

    Thanks Glenn!

+ 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. Auto-populating master schedule via drop-down function
    By sturmstories in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-09-2019, 11:56 AM
  2. Complex IF AND OR function formula
    By Reneehin23 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 01-23-2016, 01:44 AM
  3. COUNTIF Function- auto populating session counts
    By minekopanda in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-20-2015, 03:19 AM
  4. Replies: 2
    Last Post: 09-12-2014, 03:05 AM
  5. Auto populating of formula with result based on input data and party name
    By paradise2sr in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-01-2013, 02:56 AM
  6. Auto-populating COMMENTS based upon results of a formula
    By CQHixi in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-07-2012, 01:20 PM
  7. Directory lookup and auto populating file name formula
    By paperwings25 in forum Excel General
    Replies: 1
    Last Post: 11-09-2011, 02:00 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