-
VLookup with multiple requirements
I have read several community threads already, and can't seem to find what i am looking for. I have a Vlookup table that includes Pipe Type (Copper or SCH 40), Pipe Size (1, 2, 4, 6), Pipe OD (Different OD based on Pipe type) in 3 separate columns. I want to show the Pipe OD Based off the Pipe Type and then Pipe Size. I…
-
IF/OR function
Hi All, I am trying to create a formula (assuming a combo of if/or is the way to go), but keep getting "incorrect argument set. They work independently, but not when I combine them. I basically want to search for either of the following in a cell and then return a value. Look for any of the following in one column: *…
-
Count numbers separated by semi column/text in a sum formula
Hello in Column3 I have this as my first row: ="Total: " + SUM([Column3]2:[Column3]5) + " Ind & ↑: " + SUM([Column3]4:[Column3]5) * [Column3]2: 3 * [Column3]3: 1; 1 IP * [Column3]4: 4; 2 IP * [Column3]5: 0; 1 IP To keep aesthetics nice I don't want to break the cells up. Can someone help me get a formula that will add the…
-
Averages and sums in data based upon a name column
Hello, I have a Smartsheet here with some data formatted by Name, Date, Number Done, and Hours. This data is populated by forms. Here's a live Smartsheet example: https://app.smartsheet.com/b/publish?EQBCT=e6247aff028844ff9da1e4d0906860c2 Does anyone have any ideas how I can get the following: * Total Sum of [Number Done]…
-
Nested Formulas
Hello Smartsheet Community, I have been attempting to create an automated formula in the status column. I tried the following two options. =IF(AND("Complete" , IF([% Complete]4=100% , "In Progress" , IF([% Complete]4<50% , "Not Started" , IF([% Complete]4 = 0%, "Completed", "error"))))) =IF(Finish4 < TODAY(), [% Complete]4…
-
Unique Children
I am looking for a way to determine when a value in a child row is unqiur from values in other children (in the same parent). EXAMPLE: I have a sheet with four courses. A course may have children if it is offered in more than one modality (e.g., a row if offered om ground and another row if offered online). If it is only…
-
Nested IF or MATCH or JOIN+MATCH ?
Dear Community, I am trying to retrieve the information of one cell depending on several criteria: - Match week number from a date with Week Column to retrieve the payment amount - Prioritize between 2 dates (though between 2 week numbers) which one will be retrieved (Actual Payment Week will have priority over Expected…
-
Rounding full numbers (percentage) to nearest 10
I am trying to find a formula to round in full numbers (not decimal places). I have a column that creates a percentage of "investment paid", and I want to round this to the nearest 10. Ie; if the percentage result in the column is 59%, I would like this to display as 60%. And conversely, if the percentage result is 61%, I…
-
Formula Help - Automating Quarters Based on Date
Hi there, I'm trying to create a formula that will automatically pull Q1, Q2, Q3, or Q4 depending on the finish date of an activity. I worked with someone from the ProDesk, and he showed me how to create this using the normal quarters of the year. The problem is that my company's fiscal year is a bit different, with Q1…
-
Counting Unique Values in a Column of another sheet
Hi there, I'm looking to count the number of unique IDs in another sheet I'm referencing (that I cannot edit), and put the count by each ID in another sheet I'm using. I'd appreciate any tips that could help me especially keeping in mind I cannot edit the reference sheet (There are examples similar to this but they require…