Display date fields from two different sheets
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
-
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.
thinkspi.com
Answers
-
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.
-
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
-
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
-
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
-
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?
-
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
-
=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. :-/
-
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.
thinkspi.com
-
Thank you so much Paul. It's working fine now.
Much appreciated.
Jeana
-
Help Article Resources
Categories
Check out the Formula Handbook template!
