Formula help for my dashboard

Hi all

I have a sheet that I use to track recruitment activities within our team. Each role is assigned to a member of the team and has a recruitment status attached to it. I am trying to pull this information into my dashboard to show how many active roles each team member has on the go. However, I only want to select certain recruitment statuses. Is there a formula I can use in my metrics sheet where I can define what statuses I want it to pull through?

Many thanks!

Find more posts tagged with

Human Resources

Sheets

Formulas

Comments

Andrée Starå

Hi,

You'd use a COUNTIF or COUNTIFS formula for that.

Can you maybe share the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

Hope that helps!

Have a fantastic day!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

dana_hood

This isn't a SS response but felt this might help you. We are hiring a lot of people right now. We are short 250 technicians for our plant in new york. The problem was that it was taking 120+ days from the application to the 1st day of work. We built a Value Stream Map and created a "Production System". You can watch utube videos on what it is.. Basically, for each step of the process you measure the value added and non value added time and you can clearly see where the bottlenecks are. We did a Gemba visual board and every morning, we can see how many people are in the hopper and where they are in the HR hopper. We knocked the 120 days down to 100 then 80 and now 53.5 days with more to go.. Then we put the data into SS from google sheets and built a nice dashboard. You can also use lucid charts to do it.. Our HR was a black box of lack of unaccountability and then when folks see they were suppose to call back 15 people a day and they only did 10, you can see that there is a ripple effect. We literally turned this team of 30 on their head and now they are a high performing group..

-D

dcabras

Thanks for the swift response. I've attached a sample of my sheet and the sheet containing my metrics. I would like a formula that pulls information from my sheet that shows how many active roles each team member has. In order to do this I would like to include all recruitment statuses apart from 'Confirmed' so that I have a total number of active roles pulled into my metrics sheet. Does that make sense?

Capture 2.JPG

Andrée Starå

Hi Dana,

Wow! That's impressive! Nicely done!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

dcabras

That sounds great and definitely something we want to be able to monitor when we become a bit more sophisticated with the tool

dcabras

Hi Andree did you see my post above?

Paul Newcome

It looks as if you already have the cross sheet referencing steps down, so I will go ahead with the formula...

=COUNTIFS({Master Sheet Assigned to Column}, @cell = Category@row, {Master Sheet Recruitment status Column}, NOT(@cell = "Confirmed"))

Andrée Starå

I did!

I saw that Paul answered already!

Let me know if I can help with anything else!

Best,

Andrée

dcabras

I've played around with this and not having much luck. Sorry my formula knowledge is very limited. Are you able to dumb it down for me? (!)

Paul Newcome

Type in

=COUNTIFS(

then select the link in the box below the formula to "Reference Another Sheet"

Select the Master Sheet

Select the [Assigned to] column (clicking on the column header will select the entire column)

Click on "Insert Reference"

Type in

, @cell = Category@row,

"Reference Another Sheet"

Select the Master Sheet

Select the [Recruitment Status] column

"Insert Reference"

Continue typing

, NOT(@cell = "Confirmed")

Hit enter. That should be your working formula.

NOTE: Don't forget your commas. They're easy to miss when following along with the above instructions.

dcabras

Amazing! Thank you so much for your help Paul, it's actually quite simple isn't it, just need to familiarize myself with how the formulas are structured.

Paul Newcome

No matter how complicated a formula may seem, it can always be broken down into relatively simple pieces that can be tackled one at a time.

There is a template in the Solutions Center called something along the lines of Smartsheet Formula Examples (or something very similar to that). That is a great tool to use to see how functions are built. Another very helpful tool is this link below...

https://help.smartsheet.com/articles/2476176-formula-error-messages?_ga=2.66067626.529420994.1554725353-1302373248.1552411124

It is a listing of all error messages, what they mean, what causes them, and some troubleshooting tips. Between the template, the link, and the Community, there isn't much that can't be figured out eventually.

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…