HELP: Formula for Dashboard
Hello, I've currently built a form that allows people to Check-In and Check-Out, which then they'll have a drop-down and select their name.
I've also built an automated dashboard which will indicate how many people are currently checked IN in each area. Some of our staff forget to Check-Out when leaving work. How can I indicate who:
- Who has checked-IN at the post/area
- Has forgotten to check out based on the value that's "What's your name" on the dashboard?
FORM BELOW
DASHBOARD
Best Answers
-
Genevieve P. Employee AdminYes of course, no problem!
So I've built this out in thesame Intake Sheetto show how it works, but you would want your formula as a cross-sheet formula instead.
The first range you want to reference in the formula is the column that has yourNamesto return.
Then list out a column with Criteria. In my example, I'm checking the Location column for the Location named "Location A".
Then I list my second column with Criteria, which is that my helper formula from earlier indicates that this person is currently "IN".
The COLLECT function is like a filter, so I've listed my columns and what I want it to filter, and at the beginning I list what I want it tooutput(Names).
Then I close it off with CHAR(10) which is a line break, which separates the values in a Multi Select cell as you can see in the first cell above my open formula. Does this make sense for how it's built out?
When you change this to be a cross-sheet formula, you'll need to create {cross sheet references} to list each column instead of using a [direct reference] as I did here. SeeCreate cross sheet references to work with data in another sheet
Cheers,
Genevieve
-
Thank you for all your help!! I was able to add "Multi-Select", along with Location B.
Thank you once again for helping me out :)
Answers
-
Genevieve P. Employee Admin
The way I would do this is by setting up a helper column with a formula in the intake sheet. My formula would COUNT how many times that specific name appears with "Checking In" and compare that to how many times that name appears with "Checking Out".
If this number is the same, then that person is currently "OUT". However if the checking IN number is greater than the checking OUT number, this user is currently set to "IN".
For example:
=IF(COUNTIFS([What are you doing?]:[What are you doing?], "Checking in at", [What's your name?]:[What's your name?], [What's your name?]@row) > COUNTIFS([What are you doing?]:[What are you doing?], "Checking out of", [What's your name?]:[What's your name?], [What's your name?]@row), "IN", "OUT")
然后我将使用过滤掉所有的ro报告ws where this helper column says "Out", so it only shows the names who are set as "In", and Group by that name column.
You can put this grouping on a Dashboard using the Report Widget. Just some ideas!
If this hasn't helped, it would be useful to see a screen capture of your intake sheet and to know a bit more about what you're displaying on the Dashboard, but please block out sensitive data.
Cheers,
Genevieve
-
HelloGenevieve,
Once people check in we also need to capture the location that their working in... Please see the photo below for an example:
Location A
Location B
The ultimate goal would be to track on the side of location A and Location B, to display the names of the employees who are checked in. For example, Location A will have 5 names next to the chart and location B will have 6 names next to the chart.
-
Hello,
any update on the request mentioned in my previous email?
-
Genevieve P. Employee Admin
I hope you had a good weekend! Thanks for this additional information.
If you're looking to display multiple values in one cell, based on criteria in another sheet, you can use a JOIN(COLLECT formula.
For example, if you used my helper formula above to indicate if a current person is either OUT or IN, you could then use this helper column as a "filter" criteria in a formula, like so:
=JOIN(COLLECT({Column with Names}, {Helper Column with Out and In}, "IN"), CHAR(10))
With aCOLLECT Functionyou can use other filters as well by adding in another {range} and "Criteria", such as a {Location Column} and "Location Name".
I would put this formula in a Multi-Select column cell next to your number so it doesn't repeat any names. Let me know if this makes sense or if you'd like to see screen captures.
Cheers,
Genevieve
-
Hello@Genevieve P.
I hope you had a great weekend as well.
If you could send screenshots that would be awesome!! It would help me understand better.
Many thanks in advance!@Gen
-
Genevieve P. Employee Admin
Yes of course, no problem!
So I've built this out in thesame Intake Sheetto show how it works, but you would want your formula as a cross-sheet formula instead.
The first range you want to reference in the formula is the column that has yourNamesto return.
Then list out a column with Criteria. In my example, I'm checking the Location column for the Location named "Location A".
Then I list my second column with Criteria, which is that my helper formula from earlier indicates that this person is currently "IN".
The COLLECT function is like a filter, so I've listed my columns and what I want it to filter, and at the beginning I list what I want it tooutput(Names).
Then I close it off with CHAR(10) which is a line break, which separates the values in a Multi Select cell as you can see in the first cell above my open formula. Does this make sense for how it's built out?
When you change this to be a cross-sheet formula, you'll need to create {cross sheet references} to list each column instead of using a [direct reference] as I did here. SeeCreate cross sheet references to work with data in another sheet
Cheers,
Genevieve
-
Hello@Genevieve P.
I've attempted to do the formula on smartsheet but sadly the first step didn't work.
Issue 1: Once I Change the Heather from "Checking In at" to "Checking Out at", I'll automatically check her out at Location A and Location B (please see photo below)
Issue 1: Checking In
Issue 1: Checking Out
Issue 1: Formula Below
Issue 2: The multi Select Formula sadly didn't work for me.. Please see the formula that I used below:
Many thanks in advance!
-
Genevieve P. Employee Admin
I wasn't sure what your source sheet looked like - if you have it set up just like mine with different locations identified, then you're correct! We would need to adjust the first COUNTIFS formula to include the Location along with the name.
In each of the COUNTIFS you'll need to add the Column Name and then that specific cell in the row, like so:
Location:Location, Location@row
Try:
=IF(COUNTIFS([What are you doing?]:[What are you doing?], "Checking in at", [What's your name?]:[What's your name?], [What's your name?]@row,Location:Location, Location@row) > COUNTIFS([What are you doing?]:[What are you doing?], "Checking out of", [What's your name?]:[What's your name?], [What's your name?]@row,Location:Location, Location@row), "IN", "OUT")
Then for your Multi Select formula, you're missing a comma after the "IN") but before CHAR(10):
"IN"),CHAR(10)
-
Hello@Genevieve P.,
As requested, I've tried out your formula and couldn't resolve the problem. Please see photo below
-
Genevieve P. Employee Admin
You'll need to add in the
Location:Location, Location@row
insidethe COUNTIFS functions. What I mean by that is it needs to be to theleftof this closing parentheses )
So:
IF(COUNTIFS( ) > COUNTIFS( ), "IN", "OUT")
IF(COUNTIFS(....Location:Location, Location@row) > COUNTIFS(....Location:Location, Location@row), "IN", "OUT")
Does that make sense?
-
Hello@Genevieve P.
This makes sense now... I think I'm still having issues with the multi Select. Could you double-check if this is correct? (formula below)
-
Genevieve P. Employee Admin
Well-done!! Looks good!
The only thing to do now is to change the column you've titled "Multi Select" into amulti-select typeof column. This will eliminate the Duplicate names.
-
Thank you for all your help!! I was able to add "Multi-Select", along with Location B.
Thank you once again for helping me out :)
-
Genevieve P. Employee Admin
Wonderful! I'm glad you were able to get the result you're looking for
Help Article Resources
Categories
Check out theFormula Handbook template!
=COUNTIFS([Item Number]:[Item Number], OR(@cell = \"C001\", @cell = \"COO2\", @cell = \"COO3\", @cell = \"COO4\"), [Status]:[Status], OR(@cell = \"Green\", @cell = \"Yellow\", @cell = \"Red\"))<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":254,"urlcode":"formulas","name":"Formulas"}]},{"discussionID":109490,"type":"question","name":"HAS exact match within multiselect - Numbered Values","excerpt":"Scenario: Trying to identify a match if a value shows up in a multiselect from another sheet. Approach: I'm able to get 95% of this done through an index(collect(contains))) formula, but having some false positives show up wherever a partial match is found. I later found some suggestions that (has) would be more…","snippet":"Scenario: Trying to identify a match if a value shows up in a multiselect from another sheet. Approach: I'm able to get 95% of this done through an index(collect(contains)))…","categoryID":322,"dateInserted":"2023-08-25T19:26:32+00:00","dateUpdated":null,"dateLastComment":"2023-08-26T00:49:48+00:00","insertUserID":154049,"insertUser":{"userID":154049,"name":"Rob W.","url":"https:\/\/community.smartsheet.com\/profile\/Rob%20W.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-26T00:49:37+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":154049,"lastUser":{"userID":154049,"name":"Rob W.","url":"https:\/\/community.smartsheet.com\/profile\/Rob%20W.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-26T00:49:37+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":17,"score":null,"hot":3386003780,"url":"https:\/\/community.smartsheet.com\/discussion\/109490\/has-exact-match-within-multiselect-numbered-values","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109490\/has-exact-match-within-multiselect-numbered-values","format":"Rich","lastPost":{"discussionID":109490,"commentID":392694,"name":"Re: HAS exact match within multiselect - Numbered Values","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392694#Comment_392694","dateInserted":"2023-08-26T00:49:48+00:00","insertUserID":154049,"insertUser":{"userID":154049,"name":"Rob W.","url":"https:\/\/community.smartsheet.com\/profile\/Rob%20W.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-26T00:49:37+00:00","banned":0,"punished":0,"private":false,"label":"✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/KJPRLKL2FW16\/capture-png.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"Capture.PNG"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-26T00:49:35+00:00","dateAnswered":"2023-08-25T23:58:23+00:00","acceptedAnswers":[{"commentID":392688,"body":"
Hi, <\/p>
Instead of applying the formula to \"Multiselect Text String\" row, did you tried with \"Multiselect Values\" row?<\/p>
=IF(HAS([Multiselect Values]@row, [Component ID]@row), \"MATCH\", \"NO MATCH\")<\/p>
Thank you,<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":109474,"type":"question","name":"Help with date calculation formula","excerpt":"Hello, I'm trying to find a formula that will help me calculate how long an intake took to resolve. The rows I need to be calculated are Date Reported & Resolution Date. If the resolution date is blank I want it to use the current date in the calculation to see how long this issue has gone unresolved. Any help is much…","snippet":"Hello, I'm trying to find a formula that will help me calculate how long an intake took to resolve. The rows I need to be calculated are Date Reported & Resolution Date. If the…","categoryID":322,"dateInserted":"2023-08-25T16:29:39+00:00","dateUpdated":"2023-08-25T16:29:59+00:00","dateLastComment":"2023-08-25T23:01:30+00:00","insertUserID":165688,"insertUser":{"userID":165688,"name":"Nwest","title":"Systems Analyst","url":"https:\/\/community.smartsheet.com\/profile\/Nwest","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!ukHVZ18ImX4!BcjWAe8S9SY!l7iQo_PZHOx","dateLastActive":"2023-08-25T17:22:30+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":165688,"lastUserID":8888,"lastUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-08-26T17:06:33+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":23,"score":null,"hot":3385987269,"url":"https:\/\/community.smartsheet.com\/discussion\/109474\/help-with-date-calculation-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109474\/help-with-date-calculation-formula","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":109474,"commentID":392687,"name":"Re: Help with date calculation formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392687#Comment_392687","dateInserted":"2023-08-25T23:01:30+00:00","insertUserID":8888,"insertUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-08-26T17:06:33+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-25T17:04:22+00:00","dateAnswered":"2023-08-25T16:36:59+00:00","acceptedAnswers":[{"commentID":392622,"body":"
