Display date fields from two different sheets

Jeana

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!

Paul Newcome

That is because of the bold portion.

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

That should be a range pointing at the Source Sheet Asset Name/Tasks Column.

Jeana

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:

Screen Shot 2020-05-31 at 7.56.58 PM.png

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

Screen Shot 2020-05-31 at 7.56.58 PM.png

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 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.

Jeana

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

Screen Shot 2020-06-01 at 10.02.22 AM.png

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.

Screen Shot 2020-06-01 at 10.03.55 AM.png

Thanks!!!

Jeana

Paul 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)

Jeana

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 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.

Jeana

=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. :-/

Paul Newcome

That is because of the bold portion.

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

That should be a range pointing at the Source Sheet Asset Name/Tasks Column.

Jeana

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

Much appreciated.

Jeana

Paul Newcome

Happy to help! 👍️