HELP: Formula for Dashboard

therapy_mindset

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:

1. Who has checked-IN at the post/area
2. Has forgotten to check out based on the value that's "What's your name" on the dashboard?

FORM BELOW

DASHBOARD

Genevieve P.

Yes of course, no problem!

So I've built this out in the same Intake Sheet to 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 your Names to 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 to output (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. See Create cross sheet references to work with data in another sheet

Cheers,

Genevieve

therapy_mindset

Hi @Genevieve P.

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.

Hi @therapy_mindset

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")

Then I would use a Report to filter out all the rows 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

therapy_mindset

Hello Genevieve,

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.

therapy_mindset

Hello,

any update on the request mentioned in my previous email?

Genevieve P.

Hi @therapy_mindset

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 a COLLECT Function you 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

therapy_mindset

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.

Yes of course, no problem!

So I've built this out in the same Intake Sheet to 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 your Names to 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 to output (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. See Create cross sheet references to work with data in another sheet

Cheers,

Genevieve

therapy_mindset

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.

Hi @therapy_mindset

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)

therapy_mindset

Hello @Genevieve P. ,

As requested, I've tried out your formula and couldn't resolve the problem. Please see photo below

Genevieve P.

Hi @therapy_mindset

You'll need to add in the

Location:Location, Location@row

inside the COUNTIFS functions. What I mean by that is it needs to be to the left of 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?

therapy_mindset

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.

Hi @therapy_mindset

Well-done!! Looks good!

The only thing to do now is to change the column you've titled "Multi Select" into a multi-select type of column. This will eliminate the Duplicate names.

therapy_mindset

Hi @Genevieve P.

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.

Wonderful! I'm glad you were able to get the result you're looking for 😊