Display date fields from two different sheets

05/31/20
Accepted

Hi,

I'm tracking an estimated Start and End date (in a Project Sheet) and an Actual Start and End date (in a tracking sheet). In both sheets the rows where the dates are entered are CHILDREN and they both have an Asset # that I'm can match them to each other with.

I'm looking for a way to display both the Project Sheets estimated Start and End dates along side of the Actual Star and End dates, either in a report or by pulling the Actuals into the Project sheet.

I've tried a report, INDEX MATCH and even DATA MESH with no results. I'm confounded why the cells keep coming back blank or INVALID.

I'd appreciate another pair of eyes on this. I don't think it's that hard but I'm clearly missing something.

Thanks!

Best Answer

Answers

  • JeanaJeana ✭✭✭✭✭

    So I've been thinking about this more. Here's what I think needs to happen but I need help with the formulas to make it work - PLEASE!

    If an Asset # on the Project sheet matches the Asset @ on the AMT Tracking sheet I need the following to happen:

    1 - Pull into the Project Sheet the CHILDREN (11 rows that are the steps for the process) for that Asset # and the dates in the Actual Start Date and End Date columns for those CHILDREN. I think that's the issue - these are CHILDREN rows.

    Here is a pic of what I'm trying to pull into the Project Sheet:


    Here is the Project sheet I'm trying to pull data into:


    They have the same Asset # and I've tried Data Mesh with no results. They are all DATE fields.

    I've also tried an INDEX/MATCH to pull the data in based on Asset #:

    =INDEX({AMT Task Started}, MATCH([Asset #]@row, {Asset #}, 0))

    This gives me an INVALID error.

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    It looks like both screenshots are the same. Can you provide screenshots of both sheets that show the sheet and column names as well as the data? Sensitive/confidential data can be removed, blocked, and/or replaced with "dummy data" as needed.

    thinkspi.com

  • JeanaJeana ✭✭✭✭✭

    Sure thing! Thanks for taking a look Paul. :-)

    This is the tracking sheet where the Actual Start and End dates are entered. I want to pull these into the Project Sheet that has the same CHILDREN steps but with different dates.

    Thanks!!!

    Jeana

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Ok. You are going to want to add a column to the source sheet that pulls the parent row data into each of the child rows. For this example I will just call it "Parent". In this column on the target sheet, you are going to want to use a formula such as...

    =PARENT([Asset Name/Tasks]@row)


    Then in the target sheet you would use a formula such as

    =INDEX(COLLECT({Source Sheet Task Started}, {Source Sheet Parent}, PARENT([Task Name]@row), {Source Sheet Asset Name/Tasks}, [Task Name]@row), 1)

    and

    =INDEX(COLLECT({Source Sheet Task Done}, {Source Sheet Parent}, PARENT([Task Name]@row), {Source Sheet Asset Name/Tasks}, [Task Name]@row), 1)

    thinkspi.com

  • JeanaJeana ✭✭✭✭✭

    I think I follow the logic but if you could kindly provide more detail on the steps I would appreciate it.

    When you say pull in PARENT row data into each of the CHILD rows do you mean just the Asset Name so that I have that on each Child row? Like this?



  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Yes. The reason for this is that hierarchy functions can't use an @cell reference, so trying to compare something such as PARENT(@cell) = "Specific Text" won't work.

    thinkspi.com

  • JeanaJeana ✭✭✭✭✭

    =INDEX(COLLECT({Test AMT Task Started}, {Test Parent}, PARENT([Task Name]@row), [Test AMT Task Started]@row, [Task Name]@row), 1)

    This is giving me Parse error. :-/

  • JeanaJeana ✭✭✭✭✭

    Thank you so much Paul. It's working fine now.

    Much appreciated.

    Jeana

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Happy to help! 👍️

    thinkspi.com

Sign In or Register to comment.