Hi Justin - thanks so much for making these videos - I just watched your Dashboard video and got some great tips from it. However after watching this video I am confused. You say at the beginning it is going to save us hours and hours of time but I don't see where you explain how or where that time is being saved. In addition you have shown the use of INDEX/COLLECT for looking up just ONE criteria. This makes it operationally the same as INDEX/MATCH (and very similar use as VLOOKUP) however INDEX/COLLECT was designed to be used to collect information that meets multiple criteria but you don't show it being used for multiple criteria. So help an old guy out here - I am very interested in saving hours of work in smartsheet so where / how do we save many hours of work with INDEX/COLLECT and can you show it being used with multiple criteria and also show under what circumstances we would look for the 2nd or 3rd instance of the lookup? Here's a reference to best use for INDEX/COLLECT vs others on smartsheet site. www.smartsheet.com/content-center/best-practices/tips-tricks/3-formulas-look-up-data-smartsheet
Hi! yes I can create a video for a more in depth Index Collect formula - the place where it would save many hours is for an instance where you may be pulling multiple sets of data (for example, if some one chooses a client name, then their address, phone number, email address, company name, etc. automatically pulls over from a reference sheet through index collect formulas in each column based on that one name)
Hi Justin, great video and I think you might be able to help me. I consider myself an advanced Smartsheet user but I am stuck on a formula for consumption of inventory once a milestone (or workplan) is completed in my plant. I have developed workflow automation which triggers an email alert to my procurement personal from my Smartsheet schedule gantt chart that a workplan is completed which then should consume the predetermined inventory for that project. I have 4 simultaneous projects in different stages of production at once. Wondering if you want to take on a challenge? I've been in contact with the Smartsheet Prodesk who agree that what I've developed to date is incredibly comprehensive but they can't take me across the finish line in terms of the formula challenge to automatically consume the inventory on hand.
Hi Justin, Thanks so much for these videos. I'm trying to convert a process and I want to know if there is a formula in Smartsheet to help. Basically, I have questions in a form with 4 dropdown selections. Each selection will be assigned a number and then after submission, the numbers will be summed up to the total for each entry. Do you have happen to understand or know any formula that I can explore? Thanks
In the sheet that the form feeds I would just add a column and put in a SUMSIF formula for the entire column. Here is a video for creating SUMIFS formula: ua-cam.com/video/6d9UiQJ3SEQ/v-deo.htmlsi=qLj-PuQ85I-kgO7_ I hope this helps!
COLLECT is good for grabbing data based on multiple criterions, MATCH is good for grabbing data based on just 1 criteria. I prefer COLLECT in most instances.
Hi!! thank you for creating these videos. This was helpful, but I need a formula that will pull a count of a specific name into a sheet. For example how many times the name Skeeter shows up across 3 different sheets. Any help would be greatly appreciated
Hi! I would try this formula: =COUNTIFS({Sheet1 | Name Column}, "Skeeter") +COUNTIFS(({Sheet 2 | Name Column}, "Skeeter") +COUNTIFS(({Sheet 3 | Name Column}, "Skeeter") You would then just change the data inside the {} to the sheets where you are pulling the column names from.
What is the best way to create an address book/ contact list in smartsheet dashboard. Staff want to be able to find contacts information quickly based on filter option.
I would simply create a sheet with all your contact info and then create a Report to look at that sheet. Then, on your Dashboard, choose widget “Report” and put that newly created Report on your Dashboard. Then, in that widget options click on “Widget behavior” and choose “Open the data source”. That way you can view contacts on the fly on your Dashboard, and if you click on it it will drill down directly to that Report in detail if you want to drill down. On the sheet or Report, on your keyboard if you choose Crl+F (For PC) or Command+F (for Mac) it will pull up a search box you can find quickly the name you are looking for. You can also filter the sheet but right clicking on any column and choose filter (or filter in the top ribbon of the sheet). I hope this points you in the right direction.
@workflowmyworkload that's exactly what I ended up doing. But now I'm having an issue with the index collect formula. It finds majority of the data required, but for some it doesn't even though the data and criteria are correct. I'm searching for contacts based on county and title as the 2 criteria to return the name of the contract. Any suggestions?
Greate video - Im trying to do a countifs for my "Sector" column from intake sheet and count the word "internal" from the audience column from the intake sheet: this is my formula and its still not working - any help would be great!: =COUNTIFS({Audience}, [Primary Column]@row, {Sector}, Google) - this is giving invalid ref - thanks!!
Hey! I would try: =COUNTIFS({Audience}, “internal”) If that doesn’t work could you email me a screen shot of your sheet with the columns to justin@workflowmyworkload.com
Such a good video.
Hi Justin - thanks so much for making these videos - I just watched your Dashboard video and got some great tips from it. However after watching this video I am confused. You say at the beginning it is going to save us hours and hours of time but I don't see where you explain how or where that time is being saved. In addition you have shown the use of INDEX/COLLECT for looking up just ONE criteria. This makes it operationally the same as INDEX/MATCH (and very similar use as VLOOKUP) however INDEX/COLLECT was designed to be used to collect information that meets multiple criteria but you don't show it being used for multiple criteria.
So help an old guy out here - I am very interested in saving hours of work in smartsheet so where / how do we save many hours of work with INDEX/COLLECT and can you show it being used with multiple criteria and also show under what circumstances we would look for the 2nd or 3rd instance of the lookup?
Here's a reference to best use for INDEX/COLLECT vs others on smartsheet site. www.smartsheet.com/content-center/best-practices/tips-tricks/3-formulas-look-up-data-smartsheet
Hi! yes I can create a video for a more in depth Index Collect formula - the place where it would save many hours is for an instance where you may be pulling multiple sets of data (for example, if some one chooses a client name, then their address, phone number, email address, company name, etc. automatically pulls over from a reference sheet through index collect formulas in each column based on that one name)
Thank you, thank you, thank you! This totally solved a problem I was having.
Glad it helped!
Hi Justin, great video and I think you might be able to help me. I consider myself an advanced Smartsheet user but I am stuck on a formula for consumption of inventory once a milestone (or workplan) is completed in my plant. I have developed workflow automation which triggers an email alert to my procurement personal from my Smartsheet schedule gantt chart that a workplan is completed which then should consume the predetermined inventory for that project. I have 4 simultaneous projects in different stages of production at once. Wondering if you want to take on a challenge? I've been in contact with the Smartsheet Prodesk who agree that what I've developed to date is incredibly comprehensive but they can't take me across the finish line in terms of the formula challenge to automatically consume the inventory on hand.
Hey! yes I can take a stab at it, if you want to shoot the details my way at justin@workflowmyworkload.com I will look at it with you
This innovate all my solutions on smartsheet. Thanks a lot.
Absolutely! Glad I could help
@@workflowmyworkload can you consult me some steps further?
Hi Justin, Thanks so much for these videos. I'm trying to convert a process and I want to know if there is a formula in Smartsheet to help. Basically, I have questions in a form with 4 dropdown selections. Each selection will be assigned a number and then after submission, the numbers will be summed up to the total for each entry. Do you have happen to understand or know any formula that I can explore? Thanks
In the sheet that the form feeds I would just add a column and put in a SUMSIF formula for the entire column. Here is a video for creating SUMIFS formula: ua-cam.com/video/6d9UiQJ3SEQ/v-deo.htmlsi=qLj-PuQ85I-kgO7_
I hope this helps!
Hey Justin! What is the benefit of using COLLECT over MATCH in the second example (referencing another sheet)?
COLLECT is good for grabbing data based on multiple criterions, MATCH is good for grabbing data based on just 1 criteria. I prefer COLLECT in most instances.
Thank you Jason
Of course! Glad I could help
Hi!! thank you for creating these videos. This was helpful, but I need a formula that will pull a count of a specific name into a sheet. For example how many times the name Skeeter shows up across 3 different sheets. Any help would be greatly appreciated
Hi! I would try this formula:
=COUNTIFS({Sheet1 | Name Column}, "Skeeter") +COUNTIFS(({Sheet 2 | Name Column}, "Skeeter") +COUNTIFS(({Sheet 3 | Name Column}, "Skeeter")
You would then just change the data inside the {} to the sheets where you are pulling the column names from.
What is the best way to create an address book/ contact list in smartsheet dashboard. Staff want to be able to find contacts information quickly based on filter option.
I would simply create a sheet with all your contact info and then create a Report to look at that sheet.
Then, on your Dashboard, choose widget “Report” and put that newly created Report on your Dashboard.
Then, in that widget options click on “Widget behavior” and choose “Open the data source”.
That way you can view contacts on the fly on your Dashboard, and if you click on it it will drill down directly to that Report in detail if you want to drill down.
On the sheet or Report, on your keyboard if you choose Crl+F (For PC) or Command+F (for Mac) it will pull up a search box you can find quickly the name you are looking for.
You can also filter the sheet but right clicking on any column and choose filter (or filter in the top ribbon of the sheet).
I hope this points you in the right direction.
@workflowmyworkload that's exactly what I ended up doing. But now I'm having an issue with the index collect formula. It finds majority of the data required, but for some it doesn't even though the data and criteria are correct. I'm searching for contacts based on county and title as the 2 criteria to return the name of the contract. Any suggestions?
i think vlookup with match does it easily..
Promo sm
Hi! Yes I am actually in the works of promoting on social media as well soon
Greate video - Im trying to do a countifs for my "Sector" column from intake sheet and count the word "internal" from the audience column from the intake sheet: this is my formula and its still not working - any help would be great!: =COUNTIFS({Audience}, [Primary Column]@row, {Sector}, Google) - this is giving invalid ref - thanks!!
Hey! I would try:
=COUNTIFS({Audience}, “internal”)
If that doesn’t work could you email me a screen shot of your sheet with the columns to justin@workflowmyworkload.com