-
#NO MATCH when counting the results of a VLOOKUP
I am trying to count how many items fit 2 criteria, one is the year, the other is the state. The issue is coming in with the state, as I just changed it to a VLOOKUP and my formulas were all working before I did that. The VLOOKUP is working, but there are a few values which return a #NO MATCH and I can't change that due to…
-
=IF(AND formula returning "invalid Operation" error
I have several check box columns. When all are checked, I want to turn on a flag in another cell. I first tried testing two of five check box fields and Smartsheet returns "invalid operation" =IF(AND(COI:COI = "1", EFT:EFT = "1"), "1") What am I doing wrong?
-
Method/formula to rename sheets, reports and dashboards in a folder
Have a folder with 15 sheets, reports and dashboards used as a template folder. Titles include (Clear) as a generic placeholder in the folder and used in the 15. When a new project is started, we "Save As New" that folder, replace (Clear), with the project folder name and do so for each associated sheet, report and…
-
INDEX/MATCH Formula Help
Hello! I'm creating a document where I would like to index a column of dates, and return a specific date from that list. I need one of two things to occur: 1) If a date occurs in the upcoming week, I would like to return the latest date of the week 2) If no date occurs in the upcoming week, I would like to return the…
-
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 -…
