-
Formula Function - SMALL for dates with duplicates (ERROR)
Hi, I'm currently using a formula to INDEX my sheet and pick up the smallest date in a column, and then display the 4 smallest (or soonest) dates in that column to pull into my dashboard. The formula is as follows: =INDEX([Project Name]:[Status], MATCH(SMALL([Next Milestone Date]:[Next Milestone Date], 2), [Next Milestone…
-
Struggling with IF(AND....to return a checkbox
Can't seem to get this formula to return a checked checkbox =IF(AND([Total cartons]@row > 5000, [Container Type]@row = "Cart",[Load Status]@row="Complete”),1,0) any suggestions
-
Consolidating Data from Multiple Sheets
Hello, I'm hoping there's an easy way to do this. I have three sheets, they all are filled in using forms. All three sheets are used for tracking things to be billed to the same list of clients. This client list is identical between all three sheets, if it matters. I'd like a single sheet I can look at each month that will…
-
Combining multiple IF statements
Please help me solve the following The user fills in miles driven per day. Miles per day is x 1. Less than 101 miles per day = x * 0.33 2. More than 100 but less than 1001 miles per day = x * 0.30 3. More than 1000 miles per day = x * 0.25 I have the 1st and 3rd ones solved 1. =IF(AND(Miles2 < 101), Miles2 * 0.33)…
-
DAYS360 Formula
I'm trying to move various spreadsheets from Excel to Smartsheet but I keep needing the DAYS360 formula. It is very common to calculate loans based on 360 day years so monthly figures remain constant (at 30 days/mo). I was very surprised to see that this formula is not supported by Smartsheet. Is there a way to mimic it?…
-
Returning Open or Closed based on conditions
Referring to Row 6 I have a Status column and that can be Open or Closed. It depends on the condition of the Progress column which can be selected from the following dropdown menu. The first 4 conditions mean the Action Item is “Open”. The last condition means the Action Item is “Closed”. Dropdown: - Not yet submitted -…
-
Combining multiple SUMIFS into one formula
I have used and tried each of these formulas separately and they work beautifully. =SUMIFS([Total Savings]@row, Lane@row, "IL5" =SUMIFS([Total Savings]@row * 90 / 100, Lane@row, "IL4" =SUMIFS([Total Savings]@row * 50 / 100, Lane@row, "IL3" =SUMIFS([Total Savings]@row * 10 / 100, Lane@row, "IL2" =SUMIFS([Total Savings]@row…
-
Adding an additional requirement to a formula
We have been using this formula to add up the hours spent working specific clients -- been working fine. We have also been asking what category of work they have been completing at these clients but haven't been utilizing the data. =SUMIF({Kinsman - Job#1}, [Scope of Work]@row, {Kinsman - Hours#1}) I now need to add an…
-
Nested IF(AND OR(IF(AND Statement
I am trying to identify which projects need committee review vs those that can be auto-approved. I have 5 columns with the following selections: - Currently Underway? - Checked/Unchecked (I want values that are unchecked/=0) - Helper - Timeline - Returns Yes/No based on formula. (I want values that = Yes") - Helper - Count…
-
VLOOKUP cross-sheet, if the search value's parent = @row
Hi all, I'm trying to populate an entire column on a sheet using both cross-sheet and in-sheet references. I'm trying to return a value according to the following logic: Looking for cells cross-sheet that contain "Opening Date" and returning the value from the cell to it's right (also cross-sheet) if that particular…