Returns value with matching data (cross sheets)

Andrew McCreath

Your other option here is to use DataMesh.

https://datamesh.smartsheetapps.com/

Genevieve P.

https://community.smartsheet.com/discussion/comment/298314#Comment_298314

Hi @firestorm

Thank you for clarifying. Can you identify what type of metrics you're looking to pull? Although formulas can't reference reports, there are a few functions built directly into a report with the Grouping and Summary features (see here). Would you be able to use the SUM or COUNT functions to achieve your goal?

firestorm

https://community.smartsheet.com/discussion/comment/298329#Comment_298329

@Andrew McCreath I have no access :(

firestorm

https://community.smartsheet.com/discussion/comment/298330#Comment_298330

Hello @Genevieve P

The respective project members have to input their man-hours for each project across the whole month, which is why I need 1 sheet per member. I have 31 columns for dates (1st to 31st) next to the 2 columns (project reference and descriptions). These sheets are reused every month after the metrics file that is used to consolidate the total hours across all members' sheets has been backed up. And the numbers in this metrics file is used in other sheets for global consolidation, then to build dashboards.

Paul Newcome

I suggest pulling reports for each individual. If you need to pull separate metrics, you can reference the main source sheet and include range/criteria sets in you metrics formulas to only include rows where a particular name is present.

firestorm

https://community.smartsheet.com/discussion/comment/298339#Comment_298339

Hello @Paul Newcome

Problem is source data will be from both source sheet and the individual sheets for each member. Is there no way to populate these info in the individual sheets?

Paul Newcome

@firestorm I'm not sure I follow. Reports can be updated which would then automatically update the main source sheet.

firestorm

https://community.smartsheet.com/discussion/comment/298595#Comment_298595

Hello @Paul Newcome

I understand that reports can be updated and the changes will reflect in the source sheet, however, the fields that the team needs to update are not part of the source sheet, and many of them will be updating for the same project (row) so I can't create additional columns for just 1 member to input.

I have a source sheet to receive project requests with reference, descriptions, and team members in 2 columns, the main person-in-charge, and other team members (multiple contacts). There is also a project status column.

I would like to populate the "Reference" and "Description" in individual member's sheet if his/her name appears in either of these 2 contact columns, and if the project is not "closed" in the status column. For example, I have a separate sheet for Jose which will pull in the 2 project info from the source sheet, since his name appears in both columns. He will then have to update his time spent on each project on a daily basis.

These projects will also appear in the other member's sheets, e.g. Angel, Bruce.

Paul Newcome

How are your users updating their time spent each day?

firestorm

Hello @Paul Newcome

So in the individual time sheets, they will find their own projects on the first 3 columns, and there will be 31 columns for dates where they key in their hours spent each day for the projects (skipping weekends). These sheets will be reused every month (clearing all entries) after the person-in-charge has collected the total hours per project across all members using another metrics file.

firestorm

Hello @Paul Newcome any help?