-
Assistance with a formula -- similar to VLookup
I’m not sure if this is possible in Smartsheet but I know the Community will know! For our Annual Meeting, I created a blank sheet that will help us manage our schedule and assigned meeting rooms. Some of the columns are: * Meeting Room Name (the name of the hotel meeting room) * Capacity-Conference (the capacity of the…
-
Column Status Formula
Hi Everyone, I have a status column with the following options: Not Started, In-progress, Complete, N/A. I've created a formula if the parent row is set at 'Not Started' then the sub-task would automatically change to 'Not Started'. Same with Complete and N/A. In-progress needs to work a little differently. The parent row…
-
Parent Sum IF
I've tried a variety of calculations, and can't seem to get the results I'm seeking. I'm simply trying to add up all of the parent rows in a column called Resource Days that are Assigned To a specific team member. Any suggestions? =SUMIFS(PARENT(Resource Days:Resource Days), [Assigned To]:[Assigned To], "Team Member")
-
Enhancement Request: Report Criteria What? on Dropdown column like Text/Number
When I have a Dropdown column for the What? criteria of a Report, my only option is some version of the "is one of" criteria. What I want is to be able to build the criteria like a Text/Number column, for example using "contains" Use Case: I have a drop down list like this: Building 1 Building 2 Building 3 Building 4…
-
New Lookup Function
New Formulas =lookup() Find the corresponding value of data by row. For example, look up a price of an automotive part by the part number. In this release you will only be able to do lookups on the same sheet as the =lookup() formula. Until this function is usable across sheets, it is basically worthless.
-
Count & AVG only if another column contains "text"
Hi Again, After scouring discussion boards I have managed to Average the total of the rows, see below =IF((COUNT([Days to respond]194:[Days to respond]214)) = (COUNTIF([Days to respond]194:[Days to respond]214, 0)), 0, SUM([Days to respond]194:[Days to respond]214) / ((COUNT([Days to respond]194:[Days to respond]214))))…
-
Saving original cell value in another cell
I have a cell that holds a target date (Target Go-Live Date) and another cell that I want to hold the original date entered in the target date cell. This way if the Target Go-Live Date is changed, the other cell (Original Target Date) has that date saved in it. My thought was to only set Original Target Date = Target…
-
Help with nested IF statement using AND
Hello Community, I have created the below formula that SEEMS to be working but is not showing up with the color coding that typically happens when you have all your formula ducks in a row. It is returning a result, not an error, but I’m concerned there is still something missing since the text is showing up all black. See…
-
IF statement with AND/OR
I am trying to create a formula that calculates Days Overdue based on varying approval levels. I need it to calculate if Direct Manager Approval is "Submitted" and Hiring Manager/Director Approval is blank OR if Direct Manager Approval is "Approved" and Hiring Manager/Director Approval is "Submitted". Here is what I came…
-
Sum Network days but if field is blank results show invalid data type
The subject pretty well tells it all, I am trying to sum the network days between the request received and request sent date but if we haven't sent the request response as yet the field is empty and shows the error " Invalid data type" Current sum is =NETWORKDAYS([Quote Request Recieved]198, [Quote Sent Date]198) Can…
