Summary Report Capturing Data From Child Row

Samantha Jacobs

I have been tasked to create a status update report each week, showing which projects have not been updated in 6, 4, and 2 weeks, from Wednesday of the current week.

The column where I need to pull the data from has the following hierarchy:

Each Wednesday, I have to drill down the project parent, and then the child of the current month, and check to see if a status has been provided of 62 projects. If one hasn't, I have to create a new row and state that.

Currently, I have been saving the master (which I did not create, nor can I change) as a copy, adding a column selecting date as the property, going back into every project, putting the date it was last updated in that new column. From there, I manually determine which projects were last updated within 6, 4, and two weeks since Wednesday.

I offered to take the time to recreate the master sheet, but we are migrating over to Control Center, so I have been instructed to leave the master as is.

If anyone could provide any insight on how to save time on this weekly task? I will be forever grateful!

Thanks!

Paul Newcome

How are you determining the date of when each project was last updated? Do you have to look at 62 different sheets? If so, are all of those sheets laid out basically the same as if working from a template?

Samantha Jacobs

Hi Paul, the project managers are going to one sheet, finding their projects and then drill down the current month, they will add a row and then add the date and the status of their project. I have to drill down the project number, year, and month, to determine if they have updated their status' for the week, or not.

Like I said, this sheet, and the way it's ran, was not my doing. The people who did, do not want to start over because we are hoping to be migrated over to Control Center, within three months.

Thank you for your help!

Stefan

Hi @Samantha Jacobs,

would you be able to add the system column "Modified (Date)"?

If yes, you could use a report with filters looking for rows with the modified date older than 6, 4, and 2 weeks.

Other option would be to use helper columns, or an additional sheet to grab that info and make it usable for report.

Hope this helps

Stefan

Paul Newcome

I had an idea and typed it all out and then realized it won't work. Since you can't edit the original and would need to add columns, it makes the [Modified (date)] column useless because all rows would update as soon as you entered column formulas.

What are the chances that the original Master already has a [Created (date)] column on it that shows the date/timestamp for when the new row was created (meaning an update was made)?

Samantha Jacobs

Hi @Stefan,

I asked the sheet creator the same question. I can't, because she has the projects sourced to other sheets. I asked her if I could add the additional column already.

Samantha Jacobs

@Paul Newcome, the creator did set up an automation to notify her, and highlight, any cell that has been edited or rows have been added.

There is also a "Date Last Modified" column. I didn't think I could use this, because I thought it would capture the time stamp of any cell, or addition or rows, for the entire sheet.

Stefan

https://community.smartsheet.com/discussion/comment/284236#Comment_284236

Hi @Samantha Jacobs,

well, if you already created a copy, how about cell linking the entries to keep them up-to-date.

For the projects last update data, you write "going back into every project, putting the date it was last updated in that new column". I hope you do not have to look into cell history for this info!

If there is a cell in every project that you look up for the date, then you may be able to cell link it too, or fetch it by using some unique project ID in a cross sheet formula.

Hope this helps

Stefan

Stefan

https://community.smartsheet.com/discussion/comment/284241#Comment_284241

Hi @Samantha Jacobs,

ahh, you can use the column. It shows individual dates for every column.

Greetings

Stefan

Samantha Jacobs

@Paul Newcome or @Stefan, do either of you know if I could create my own sheet with "last modification made" column, source the master, and telling me when the last modification was made on the Project ID column from there?

Stefan

https://community.smartsheet.com/discussion/comment/284246#Comment_284246

Hi @Samantha Jacobs,

yes. You already have your own copy of the source sheet. Like I wrote above, cell link the "Date Last Modified" cells to the column in your sheet.

From there you can do whatever you want with the data in your sheet.

Hope this helps

Stefan

Samantha Jacobs

@Stefan,

Yes, I have to go into 62 projects drill down to the current month and see if an update has been made for the week, or not. To me, it is a waste of time. That's why I reached out on here, to see if anyone could help me get back an hour and half of my day each week. :)

Paul Newcome

The problem is that hierarchy functions can't use @cell functions. So a formula with a cross sheet reference to pull the most recent "last modified" date for a project's child row won't work without adding to the original sheet.

Cell linking or any other formula to pull the data over to another sheet presents problems with hierarchy having to be manually maintained.

Let's see here... Taking a look at the data within the [Project ID] column, will the project ID's always be "yyyy-###"? If so, do you have any entries earlier than the year 2000?

Samantha Jacobs

@Stefan, sorry, I asked the question before I saw your comment.

@Paul Newcome, that is what I thought too, about the formula. Yes, the ID is always "yyyy-###". No, there no ID's earlier than 2000.

I am trying to think of this as a blessing in disguise, because I have learned a lot about Smartsheet trying to figure this out. On the other hand, I cannot wait to start using Control Center and starting over.

Stefan

@Samantha Jacobs,

what a waste of time ;-) In the 21st century! With Smartsheet !!! ;-)

Do you really need to have 3 entries for every project? Or would it be ok to have an additional column (symbols) or conditional formatting to clearly show if a project has not been updated since 6, 4, or 2 weeks?

Greetings

Stefan

Paul Newcome

Ok. Let's start with creating the base sheet for you. Then I will work on some of the details on how to flag the records once we get everything pulled with some helper columns in the new sheet that will help us work without hierarchy...

[Index Row] (text/number column):

Manually enter

1

2

3

4

5

etc...

until you have covered more rows than you anticipate ever needing (think future rows being added and then go beyond that number just in case). This will make it so that as new rows are added to the master they are pulled over to your sheet immediately and will maintain the same order as the master.

[Project ID] (text/number column):

Column formula of...

=INDEX({Master Sheet Project ID Column}, [Index Row]@row)

Modified (text/number column):

Column formula of...

=INDEX({Master Sheet Last Modified Column}, [Index Row]@row)

[Project Marker] (checkbox column):

Column formula of...

=IF(AND(LEFT([Project ID]@row = "2", LEN([Project ID]@row) = 8), 1)

[Parent Help] (text/number column):

Column formula of...

=MAX(COLLECT([Index Row]:[Index Row], [Index Row]:[Index Row], @cell < [Index Row]@row, [Project Marker]:[Project Marker], @cell = 1))

[Date Help] (date column):

Exactly how is your Modified date displayed? mm/dd/yy or mm/dd/yyyy or dd/mm/yy or dd/mm/yyyy or something completely different? I ask because we are going to use a column formula to extract the date from the text string in the Modified column, but the exact formula will depend on the format of the date in that cell.

[Last Modified (date)] (date column):

Column formula of...

=IF([Project Marker]@row = 1, MAX(COLLECT([Date Help]:[Date Help], [Parent Help]:[Parent Help], @cell = [Parent Help]@row, [Index Row]:[Index Row], @cell > [Index Row]@row)))

[Most Recent Entry] (text/number column):

Column formula of...

=IF([Project Marker]@row = 1, INDEX(COLLECT([Project ID]:[Project ID], [Parent Help]:[Parent Help], @cell = [Parent Help]@row, [Index Row]:[Index Row], @cell > [Index Row]@row, [Date Help]:[Date Help], @cell = [Last Modified (date)]@row)))

Now you should have a replica of the master sheet (minus hierarchy), but on each project row (rows that start with "2" and have 8 characters) [Last Modified (date)] and [Most Recent Entry] will contain the date and update for the most recent update.

Since you have actual dates now, you can build your reports for 6, 4, and 2 weeks, point it at your copy and set up your criteria, display just those 3 columns on the reports, and then just forget about it.

As long as you pre-filled that first column with enough numbers, you really shouldn't have to worry about it again since the helper columns replicate the master exactly based on that.

You may want to set up a separate helper column (text/number) and in row 1 enter

=MAX(COLLECT([Index Row]:[Index Row], [Project ID]:[Project ID], @cell <> "")

Then set up an alert that whenever that number changes to any number close to the highest number you entered, you get an alert letting you know to add new rows which is as simple as adding more numbers (column formulas will automatically populate as you add them, so no need to do anything else there).

For example, lets just say you pre-filled that column with 1 - 100. You can set up an alert to trigger when the number above reaches 75 to let you know you are almost out of row and need to add more so that all data from the master sheet is in fact captured.

I hope all of that makes sense... Please let me know that date format mentioned above and if you have any questions at all.