+ Reply to Thread
Results 1 to 5 of 5

How to bring up the target value from other table when selecting corresponding task

  1. #1
    Registered User
    Join Date
    08-30-2009
    Location
    melbourne
    MS-Off Ver
    Excel 2016 / 2019
    Posts
    84

    How to bring up the target value from other table when selecting corresponding task

    Hi,

    I am trying to make a monthly performance progress table.
    Attached is a sample.

    Basically, I have created 2 tabs - one for task, the other for raw data (or call it progress record)
    In Data tab, when i enter date and select 'Task' during drop down list, I want to bring the related target value that is set in the Target tab in relation to Month (or date).
    We have two types of target - qty and completion date
    Later, I am going to make a pivot table so that I will make a monthly summary in graph or progress chart as per task.

    Thank you for your help in advance.
    MK
    Attached Files Attached Files

  2. #2
    Forum Expert WideBoyDixon's Avatar
    Join Date
    10-03-2016
    Location
    Sheffield, UK
    MS-Off Ver
    365
    Posts
    2,182

    Re: How to bring up the target value from other table when selecting corresponding task

    I made changes to column B to make this easier. Formula for B2 is now:

    Please Login or Register  to view this content.
    Copy that down the column as necessary. Number format for column B is now mmm-yy. Then formula for E2 is:

    Please Login or Register  to view this content.
    Change "Z" and "100" to match your appropriate range. Copy the formula in E2 down the column. Attached for reference.

    WBD
    Attached Files Attached Files
    Office 365 on Windows 11, looking for rep!

  3. #3
    Registered User
    Join Date
    08-30-2009
    Location
    melbourne
    MS-Off Ver
    Excel 2016 / 2019
    Posts
    84

    Re: How to bring up the target value from other table when selecting corresponding task

    Thanks WBD,

    Is there any way to bring exact value?
    For example, E7 is meant to be date, but brought general number.

    Please advise.

    MK

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: How to bring up the target value from other table when selecting corresponding task

    Assuming that there will be fewer than 43,001 tasks, try applying the following formula as a conditional formatting rule to E2:E7 of the file attached to WideBoyDixon's post.
    =E2>43000 and then set the number format to date.
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  5. #5
    Forum Expert WideBoyDixon's Avatar
    Join Date
    10-03-2016
    Location
    Sheffield, UK
    MS-Off Ver
    365
    Posts
    2,182

    Re: How to bring up the target value from other table when selecting corresponding task

    Quote Originally Posted by JeteMc View Post
    Assuming that there will be fewer than 43,001 tasks, try applying the following formula as a conditional formatting rule to E2:E7 of the file attached to WideBoyDixon's post.
    =E2>43000 and then set the number format to date.
    Let us know if you have any questions.
    Yeap. I like this solution

    WBD

+ 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. Replies: 2
    Last Post: 01-13-2020, 05:51 PM
  2. Pivot table summarizing time spent per task per day from a task log
    By Fattyfatfat Kid in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 04-29-2019, 08:38 PM
  3. Bring SQL tables in to Excel by selecting Table name
    By vinaynaran in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-09-2016, 04:00 AM
  4. Choose a random task after selecting a person and time available.
    By tonymarsh in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-15-2016, 10:26 PM
  5. Replies: 1
    Last Post: 08-17-2015, 05:02 PM
  6. Selecting next available row in target column
    By jazbath in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-11-2013, 08:20 PM
  7. Selecting Range with Target cell
    By Demon4231 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-14-2011, 12:17 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