Summary Report Capturing Data From Child Row

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?

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!

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

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

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.

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

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

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

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

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

Greetings

Stefan

@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?

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

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

@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. :)

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?

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

@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