-
Formula for Status
I would like to auto create a status based on an end date, completed date, cancelled checkbox or on hold checkbox. If end date is in the future an neither checkbox is selected then "Open". If the end date is in the past then "Overdue". If complete date has been entered then "Complete". If cancelled checked then…
-
Automate formula
Is there a formula that can do the picture below.... the Blue unlined column is provided in the form request, The Black underline column needs to recognise the contract id, then find the last V number allocated and allocated the next number.... so that the Red underlined column, when joined creates the variation ID…
-
Count Hierarchies without including dupes
Is it possible to count the number of the hierarchies? Right now, I'm using the count function and manually clicking on level 1 for those counts and level 2 for those counts. My document is rather large and I don't want to click through each one and someone may insert a new level and I don't want a break in my formula. The…
-
Unparseable Error
Dear Smartsheet Community, Very new to Smartsheet and having trouble with 2 formulas that work in Excel. =IF([Date Approved] = 0, [Date Calculation],"Completed") =NOW() - [Date of Submission] Could you kindly let me know if the formula structure is incorrectly done? Would appreciate your input. Thank you. Best, ~ Rowenna
-
ISBLANK or 0 (ZERO) : Formula for a column
Good afternoon, I have the following formula that is populating my "HRS Left" column =IF(OR(ISBLANK(BAC@row), ISBLANK(HTD@row)), BAE@row, SUM(BAC@row - HTD@row)). But I need to be able to write the formula so that the "HRS Left" column will populate if my "HTD" or "BAC" column is "0" (zero) or ISBLANK Thank you
-
Hi, can anyone help me to get my Green/Yellow/Red/Blue Status to work correctly?
I have to write a green/yellow/red/blue status indicator with a risk override field as well. The concept is that if the Risk Override field is populated then that should be the status. The risk override field will be a drop down between Red and Yellow. If that is not populated, then it should be Blue if % complete is 1…
-
Can I create a hyperlink by combining text strings and fielded data? Pretty basic parameter work
I have fields: email: subject: body: I want to use this hyperlink to open an email with those parameters: <a href="mailto:myemail@email.com?subject=This is a subject. You can't use certain characters in here. But we should be able to control that.!&body=Here is the body. Same problem with special characters.">Link to send…
-
Using SUMIF to have a blank cell if data comes back zero
I am pulling data from another sheet. I am using a SUMIF to sum things off a certain date and if it meets a certain name. When it has no data to bring back, it shows a zero and I would like for the cell to be blank so that my charts do not show a lot of zeros. How can I do this? Thanks =SUMIFS({AIC-Diverts 2020 Range 1},…
-
Formula to show text next to sum amount
Good day, Our Currency ( BHD for Bahrain Kingdom ) is not one of listed currencies and I wish to show it to amount numbers , how to have Currency BHD seen next tp total sum in below formula ? =SUM([Service Charge]4:[Service Charge]13) Also , is there a way or formula to show text ( BHD ) or any other text next to number…
-
Copy Row Automation causing duplicates
I am using an automation that copies rows which contain the word "submittal" into a sheet where we are tracking submittal dates for all our projects. I cannot use a report because we need to be able to publish to a calendar. Therefore, the end result must be a sheet. As most of you probably know, these dates can change…