Best Of
Re: Sumif formula for current month invoices
Try this formula:
=SUMIF(Date:Date, MONTH(@cell) = MONTH(TODAY()), Invoice:Invoice)
I hope this helps.
Cheers,
Ramzi
Re: % complete on Gantt view
That option is in the settings wheel on the upper right. Screen shots are below. I hope that helps.
Re: Flip rows and columns
There is currently no way to programmatically change column headers. But if you are talking about pivoting rows to columns there are three ways that you can do it.
- With formulas to reference row values into columns cell values and then do a copy paste special/values. So you can convert row values in the first column of a sheet to columnar values in the first row of a sheet.
- Copy the data to Excel and use the copy/paste special/pivot and then copy and paste back into Smartsheet
- Look into using the Pivot add-on app for Smartsheet
- I hope this helps.
Cheers,
Ramzi
Re: Update a row with a form
I have something like this for our companies vacation requests. the request comes in through a request form. This will create the row and populate the ID using a system generated number. Any changes to that requires the user to enter the ID then enter the updates they want. The updates go into a second sheet. The original sheet 3 columns for each value (this is messy but it works) 2 of the columns are hidden and one is the display.
for example Start date there is
Requested Start date (this is what the original request is) it is hidden
Change Start date (this using an Index/Match to locate any change request on the 2nd sheet) it is also hidden
Start Date (this uses an IF statement to say IF(ISBLANK([Change Start date]@row),[Requested Start date]@row,[Change Start date]@row) this is the column is displayed
this will look for any change request for that column and display if it is changed otherwise will display the original data.
one final not is it only works if the 2nd sheet for change request is set up to have new entries added to the top of the sheet. That way the latest changes for each column will be displayed.
Re: IF Function that produce multiple "value_if_true" outcomes
Happy to help! 👍️
Just a quick note... If you want a line break in between the names so that it looks more like the multi-select dropdown when text wrapping is enabled, your delimiter would be CHAR(10).
Re: Index MATCH vs COLLECT (matching 2 values, returning a different value)
Thanks for the vote of confidence. Haha.
I am going to suggest a COLLECT function instead of the MATCHes.
=INDEX(COLLECT({Migration Task Plan Range task%complete}, {Migration Task Plan Range brand}, BRAND@row, {Migration Task Plan taskname}, "Sitemap & Navigation Finalization"), 1)
Re: formula to populate due dates
You need a row reference for your status column Try this:
=IF([Status]@row = "Bar layout needed", TODAY() + 2)
I would also consider putting the values in a separate lookup sheet (or same sheet in tucked away columns) and then do a lookup based on the Status value. That way you can have unlimited statuses and be able to make adjustments to the number of days without changing your formulas. Would also make the formula much smaller. You can use VLookup (shown in example below) or Index/Match.
Two columns to have your lookup/value pairs (green shading), and then the two columns that have your status selection and the calculated date.
Formula you would use in the Date columns:
=IFERROR(TODAY() + VLOOKUP(Status@row, $Lookup$1:$[Value (days)]$5, 2, false), "")
I hope this helps.
Cheers,
Ramzi
Re: IF Function that produce multiple "value_if_true" outcomes
If you are using a separate table to reference where each department is listed on its own row and the next column over contains the analyst assigned, you could use a formula such as...
=JOIN(COLLECT({Table Sheet Analyst Column}, {Table Sheet Department Column}, CONTAINS(@cell, [Departments Column]@row), "delimiter")
If you could have the same analyst listed multiple times for different departments and want to only have them displayed once to keep things looking a little cleaner, we could work in a DISTINCT function.
=JOIN(DISTINCT(COLLECT({Table Sheet Analyst Column}, {Table Sheet Department Column}, CONTAINS(@cell, [Departments Column]@row)), "delimiter")
Re: how to get a form on smartsheet to automatically send to a certain manager based on type
The INDEX/MATCH formula will be like this:
=INDEX({Manager Range}, MATCH([Type Request]@row, {Type Request Range}),0)
For this to work, you're gonna need to have a database sheet where you list all the type requests and have the manager you want to link to each type. It's basically a 3 column sheet, where you're not gonna use the Main column (because it's always a type/number column). Make the manager column a contact list type of column and the Type request a single dropdown list column. Have both ranges linked to each column.
Then in your form sheet, make sure the type request column is a single dropdown list that exactly match the items of the helper sheet. Same for the manager column.
Add the formula within the manager column and make sure this column doesn't appear in the form to prevent users for putting names straight forward, which you don't want to happen.
Hope it helped!
Re: Is there a keyboard shortcut for filling in the 1 - 5 Star Rating in a Symbol Column?
If I understand you correctly, you want the user to enter a number and get a symbol to match. If so, you can do this:
- Create a value column for the user to enter a number from 1-5 (in this example it's called Value)
- Put this formula in your Rating column: =IF(Value@row = 1, "One", IF(Value@row = 2, "Two", IF(Value@row = 3, "Three", IF(Value@row = 4, "Four", IF(Value@row = 5, "Five", "")))))
I hope this works for you.
Cheers,
Ramzi