+ Reply to Thread
Results 1 to 2 of 2

Adjusting Cells Based On Completion

  1. #1
    Registered User
    Join Date
    03-16-2017
    Location
    USA
    MS-Off Ver
    2010
    Posts
    4

    Adjusting Cells Based On Completion

    I have a two sheet work in which Sheet2 shows all the administrative data and Sheet1 shows only the data an employee would need to know. Sheet1 contains multiple countdown dates pulled from Sheet2. What I would like to do is continue to have Sheet1 pull forward the countdown dates from Sheet2.... But, once the the employee completes the item and is updated on Sheet2, I want Sheet1 to replace the countdown date with specific text such as "Received" or "Closed" and if possible, in the same cell, bring forward the date on Sheet2.

    The attached is just an excerpt from the much larger workbook.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    05-20-2015
    Location
    Chicago, Illinois
    MS-Off Ver
    2016
    Posts
    2,103

    Re: Adjusting Cells Based On Completion

    Take a look at the attachment to see if I'm close. I use the following formula, array entered (Ctrl + Shift + Enter instead of Enter) in sheet1 A2 to pull a current list of the order numbers of the master:

    =IFERROR(INDEX(Sheet2!$A$2:$A$40,SMALL(IF(Sheet2!$A$2:$A$40<>"",ROW(Sheet2!$A$2:$A$40)),ROW(1:1))-1),"")

    This list will automatically update as you change the master sheet. Then in B2 (non-array):

    =IFERROR(IF(INDEX(Sheet2!$D$2:$D$40,MATCH($A2,Sheet2!$A$2:$A$40,0))="",INDEX(Sheet2!$C$2:$C$40,MATCH($A2,Sheet2!$A$2:$A$40,0)),"Shipped "&TEXT(INDEX(Sheet2!$D$2:$D$40,MATCH($A2,Sheet2!$A$2:$A$40,0)),"dd mmm yy")),"")

    and similarly in C2:

    =IFERROR(IF(INDEX(Sheet2!$F$2:$F$40,MATCH($A2,Sheet2!$A$2:$A$40,0))="",INDEX(Sheet2!$E$2:$E$40,MATCH($A2,Sheet2!$A$2:$A$40,0)),"Complete "&TEXT(INDEX(Sheet2!$F$2:$F$40,MATCH($A2,Sheet2!$A$2:$A$40,0)),"dd mmm yy")),"")

    and a basic lookup in D2:

    =IFERROR(INDEX(Sheet2!$G$2:$G$40,MATCH($A2,Sheet2!$A$2:$A$40,0)),"")

    Fill all four formulas down as far as you need. If you'll have more than 40 entries on the master sheet, then change all of the $40s in the above formulas to something more suitable. This should automatically project current data from the master onto sheet1, with the requested complete/shipped notifications. Hopefully this helps?
    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. Copy rows to new tab based on date and completion.
    By maxwema in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-09-2016, 11:35 AM
  2. Force cell completion based on another Cells Value
    By PaulCooke in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-16-2015, 08:59 AM
  3. Percentage completion based on Today(Date)
    By shivamuniyappa in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-05-2014, 02:30 AM
  4. vacation accrual based on completion of 90 day probation
    By Ashstewart in forum Excel General
    Replies: 12
    Last Post: 12-01-2013, 05:37 AM
  5. Replies: 5
    Last Post: 07-04-2013, 03:04 PM
  6. [SOLVED] Percentage based on the completion of the milestones
    By chriseverclear in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-31-2012, 12:16 PM
  7. Calculation of a percent completion based upon due date
    By Anna Mainhart in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-21-2012, 12:16 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