Best Of
Re: February Question of the Month - Join the conversation and receive a badge
If you could create a new badge in Community, what would it be?
I have actually given this one a lot of thought. I would create a "dog with a bone" badge. I watch members in the community commit to helping someone by giving it their all, sometimes over the course of days and I love seeing how they do not leave people hanging. They are like a dog with a bone! ποΈ
Yvonne Smythe
Re: Community Corner Newsletter [February 2025]
Love the pics @Marcela. I remember you talking about Costa Rica during Jumpstart - and you weren't kidding about how beautiful it is!
Will Duplex
Re: JOIN COLLECT formula
Hi @Rebecca Panaccione, (and nice to hear from you again!)
You can use a JOIN(COLLECT formula to bring together the information from multiple cells in one sheet to another.
However, before bringing over the dates from your source sheet, you will want to list out the full date range in one cell in the source sheet (such as 1/31/20 - 2/4/20). To do so, you can set up a helper column in your source sheet to just JOIN this information, first:
=JOIN([Start Date]@row:[End Date]@row, " - ")
This will bring your date range together with the " - " symbol, as you have it formatted in your current demo.Then you can create your JOIN(COLLECT with cross-sheet references, referencing this helper column:
=JOIN(COLLECT({Total Date Range in Source Sheet}, {Email/Contact in Source Sheet}, [Email/Contact]@row, {Total A/L in Source Sheet}, >=1), " , ")
You will notice that I built in two different criteria. The formula should read as follows:
- Join together the content of the cells in the column "Dates Together"
- IF
- The Contact in the source sheet matches the contact of my current sheet, in this row
- The Total A/L column in the source sheet has a count greater than or equal to 1
- (this way it won't add in any of your S/L or M/L dates)
Here are some Help Center articles that I used to create this formula:
- JOIN function
- COLLECT function
- Hide Column (for your Helper column)
- Cross-Sheet references
Let me know if you have any questions about this!
Cheers,
Genevieve
Genevieve P.
Re: Auto direct risk from master register to project risk
I figured it out..
For those wondering, I had a parent workflow like so;
Then I had a child workflow like so;
Super super happy!
Ide AirTrunk
Re: Pulling values from a dropdown list
Hi @ScottPinson_AlliedSolutions
Try this formula. It replaces the line feed CHAR(10) with the comma.
I hope it helps!
=SUBSTITUTE(Category@row, CHAR(10), ", ")
Gia Thinh
Re: Stop Calculating Days After a Cell Changes
To make it easier try breaking your IF function into the 3 separate parts/functions it should have, to write those out logically, and then combine into the final formula by putting commas between them. If, Then, Else/otherwise:
If Assigned date is blank: ISBLANK([Assigned Date]@row)
Then calculate networkdays between created date and today: NETWORKDAYS([Created Date]@row, TODAY())
Else/otherwise calculate networkdays between created date and assigned date: NETWORKDAYS([Created Date]@row, [Assigned Date]@row)
Put them all together to get: =IF(ISBLANK([Assigned Date]@row), NETWORKDAYS([Created Date]@row, TODAY()),
NETWORKDAYS([Created Date]@row, [Assigned Date]@row))
Here's the IF function page if it's helpful to read through:
Courtney S.
Re: Filling a Contact List from another sheet
Greetings Jake,
In my opinion, the easiest way to do this would be using something like the following formula:
=IF(ISBLANK([Sup/Mgr cell with name]@row), "", INDEX(COLLECT({Contact list }, {Name}, CONTAINS([Sup/Mgr cell with name]@row, @cell)), 1))
Contact List = the sheet and column from your contact sheet you want to display if the condition is made.
Name = the value you are comparing your Sup/Mgr sheet to on your contact list.
The ISBLANK component ensures that if the space is blank, nothing happens.
I hope this helps.
Frank S.
Re: The Community Valentine's Day cards are back! π
Send much Valentine warm and fuzzy to @Michelle Choate 2! β¦and her two dogs!
Michael Drayton
Re: How can I open multiple sheets in Smartsheets?
When you are folder view (choose the browse icon that looks like a folder), you can right-click to open items in a new tab. I generally keep one tab in the main folder view and open everything else the way I just described.
Hope this helps,
Dave
DKazatsky2
Re: If Row is not empty, then cell equals value from another sheet
So you are getting the circular reference because you are evaluating the cell that you put the formula in. But I don't think the Match formula is going to do what you want it to do.It just returns a numerical value of the location of a cell in a table.
I think the formula you need to use is probably just a vlookup, but for that to work you have to have a unique project ID or something to help it find the project name from the other sheet. How are you identifying the project names?
Adam Costello