Reference a Report

I am using a form to track time spent on projects. Right now, they select the project from a dropdown list that I manually created and then they type in the task and the hours worked.

Ideally, I would like for the Task Name to populate according to those available based on the Project selected. For example, if the user selects System Website from the Project list, I would like the Task list to generate all tasks that exist on that Project grid, so they can specifically charge time to a task.

I think that formula might be unwieldy so what I'm thinking is I could create a Report that populates any tasks that aren't marked as 100% Completed. That would pull all tasks from all projects into one sheet.

Is it possible to reference a report, and not just another sheet? It doesn't appear possible.

Find more posts tagged with

Forms

Education

Formulas

Project Management

Cell linking

Comments

Andrée Starå

Hi,

Unfortunately, it's not possible at the moment, but it's a great idea!

Please submit an Enhancement Request when you have a moment.

A possible workaround could be to have the form on one side in a dashboard and the sheet or report on the other and in that way they could see what's available.

Would that work

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Paul Newcome

KWright84,

I may have a work around. It would require a little bit of work setting it up, but it will change a displayed list of tasks based on the project selected. As of right now, I only have it displaying the list of tasks. It would still require manual entry of the task itself into a tracker sheet, but at least it would provide consistency of tasks entered along with serving as a kind of searchable database for all tasks based off of the project selected.

Depending on how in depth and complex you want/are willing to get, we may be able to build in some sort of tracking that will show you a total of what has been charged to each task and other pertinent info. That would require a fair amount of work and time though.

KWright84

Hi Paul,

I'd love to hear about the workaround! I'm willing to get as complex as needed to solve this problem and ensure the work being logged is against the specific tasks, rather than just making people write the task in themselves. I'm imagining adding another column to the project page for "Work Hours" which is updated each time someone submits hours against that task.

Paul Newcome

Could you provide an email address I can share a sheet to for you? It will be MUCH easier to explain with actual examples as opposed to trying to just explain here. It basically uses a master sheet of projects and tasks and uses an INDEX/MATCH system to display the correct listing of tasks based on the project selected.

KWright84

katherine.wright@yesprep.org

Paul Newcome

Ok. I will get something to you within the next couple of days.

Sign in to join the conversation!

Quick Links

Trending discussions

Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?
Need Help with If, Index, Collect
Please help, I have a few of these to do. I have a nested IF formula. The first part is an INDEX/MATCH and works as a standalone. The next few are INDEX/COLLECT. These are not working as standalone or in the nested version (Unparsable Error). Any ideas? =IF(CONTAINS(“A/V”, [Vendor Type]@row ), INDEX(COLLECT({Date Secured…