Count If referencing other sheets
Hi! I am looking to create a Count If function that references other sheets.
Using the example below, in the Opportunity Tracker sheet, I am looking to create the function in the Staff Allocated column. For each unique Client + Opportunity Name pairing, I want to determine the count of resources "Accepted by client". Each of the other sheets are named based on the Client column and they would need to reference the Opportunity Name and Engagement Status columns in those client sheets.
For reference, here's what the results should yield.
Thank you!
Answers
-
How many different client sheets do you have, and what is the end goal for pulling these counts together?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Right now we only have 4 client sheets but this will continue to grow over time. I wasn't sure if the capability existed to reference only the specific sheet where the client Column = sheet name.
The end goal being that we can identify how many resources have been allocated to open projects to then identify any variances. For background, each client opportunity will have a total requested staff amount which we would use with the # of allocated staff to determine the number of open recs.
-
My suggestion would be to create a helper column in each of the sheets that has a basic column formula to output the client name on every row
="ABC"
Then you would create a row report that references each of the client sheets and is filtered to only pull rows of that specific status.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Thank you- that makes sense! From there, is there a way to pull the count from the report into my Opportunity Tracker sheet?
-
The report would replace the opportunity tracker sheet.
My personal suggestion though would be a total restructure of your build where you have a master sheet that contains all clients and then use reports or Dynamic Views to share the filtered lists out with whoever needs to see them as needed. Then pulling your higher level metrics would be much easier to manage.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!