Populating Entire Columns Referencing Another Sheet
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!