Populating Entire Columns Referencing Another Sheet

Muhammad

Hi all!

Over the last week or so I have been trying to build a report that shows average tasks completed over a given 12 week period. So far I have been successful in creating the formulas for these, which have been working on dummy data. Now I would like to process live data from another sheet. Well ideally I would reference this data from a weekly report that is generated automatically, as the report already organizes all the data I need very nicely, however it would seem referencing reports is currently impossible in Smartsheet. As this is the case, I would like to reference live data from the sheets the report currently references. The problem is, there are a lot of blank spaces (due to the task pending completion) and the rows have related finish dates that must also be captured (it will become more clear with screenshots). Essentially what I would like is to capture 2 columns and populated another sheet with these columns, however the values should only be captured IF they are filled with data. I need to capture these columns so I can calculate the weeks of completion and the averages per week. Please find screenshots with some descriptions below:

The above is my new sheet, where dummy data and outputs are labeled accordingly.

The above is a screenshot example of what the live data looks like. The non-blank cells are tasks that have been completed along with the date completed. I would also like for my sheet to automatically update when a task is completed in the source sheet (the blank cells will be filled as those tasks are completed).

Thank you very much to everyone who has helped me build this sheet so far, if it was not for this community, I would not have been able to come up with the formulas I needed to bring this automation idea as far as it has come. Thank you all for the support, now and in the future, any help is greatly appreciated!

Genevieve P.

Hi @Muhammad

My apologies for the delay, I missed this notification! I understand that you need to reference data, and that a Report cannot be referenced.

The second sheet would need to have one column that will help match the data across sheets. It looks like you have this set up with a Date column called "Week Of", which should work for your purposes! Once you have the week you're looking for, you can use WEEKNUMBER to identify if the dates in your source sheets match the weeknumber in your current row.

For example, in your AVG formula, you could use it like this:

=AVG(COLLECT({Column to Avg}, {Date Column}, WEEKNUMBER(@cell) = WEEKNUMBER([Week Of]@row)))

Let me know if I've still misunderstood what you're looking to do! if so, it would be helpful to see all the current formulas you're using, and identify which one needs to be updated.

Cheers,

Genevieve

Genevieve P.

Hi @Muhammad

Would you be able to show an example of what you want the end goal to look like?

I have to say it does sound like a Report would still be best. I would create a Report and then filter by the date column, taking away blank rows as well as filtering by your date range. This would keep your rows "live" so they update as their data is updated, as well as automatically including in new rows when they suddenly are no longer blank.

Then you could have additional columns in the main source sheet that create your calculations, which you can have hidden in the sheet but made visible in the Report. You could even potentially Group and Summarize your data in these hidden columns, if that's what you're looking to do. Let me know if that makes sense!

Cheers,

Genevieve

Muhammad

Hi @Genevieve P. !

Yes, so ideally my end goal would look exactly like the dummy data provided in the image above. I would use a report to do this, and the company does in fact use a report to do exactly what I'm trying to do elsewhere, however my task requires that it be a sheet. This is because the company is looking to automate the processing of the data gathered in the report, and for this I need to be able to reference that data. Unfortunately it seems that referencing reports is impossible for some reason on Smartsheet, so I am am left with attempting this solution. What I was to do is reference all the live data on the 2 other sheets, and perform the avg, count, and week of operations on them. I will THEN generate a report with these values presented in a formatted manner (No repeat values, for week of, avg, or count). So yes to reiterate, I want to reference the data in 2 other sheets, perform the above operations (labelled as output) on this data, THEN produce a report after having processed the data. Let me know if I have not been clear, or if there is anything else I can do to clarify. Thank you so much for taking time out of your day to respond, I really appreciate it!

***Note: The final report will look something like this:

^^^All of the above values are outputs of formulas performed on the "Engineering Date Completed" and "Engineering Days to Complete" columns, found on the sheets to be referenced.

Genevieve P.

Hi @Muhammad

My apologies for the delay, I missed this notification! I understand that you need to reference data, and that a Report cannot be referenced.

The second sheet would need to have one column that will help match the data across sheets. It looks like you have this set up with a Date column called "Week Of", which should work for your purposes! Once you have the week you're looking for, you can use WEEKNUMBER to identify if the dates in your source sheets match the weeknumber in your current row.

For example, in your AVG formula, you could use it like this:

=AVG(COLLECT({Column to Avg}, {Date Column}, WEEKNUMBER(@cell) = WEEKNUMBER([Week Of]@row)))

Let me know if I've still misunderstood what you're looking to do! if so, it would be helpful to see all the current formulas you're using, and identify which one needs to be updated.

Cheers,

Genevieve