-
Index/Match not working when the Match column in source doc is a 'RIGHT' formula
I have a column in my source sheet where Store # is a RIGHT formula. When I index match in my Target sheet I get 'NO MATCH' when trying to find Store #.
-
SUMIFS not working after certain dates
Hi, I have been working on daily spent in Sheet 1 and that spent is converted to weekly spent directly using SUMIFS function, but after certain weekly dates the Total Spent SUM is been showing wrong. Sheet 1 data Sheet 2 Data The formula used is…
-
Indexing and collecting information from non adjacent columns in original sheet
I want to pull information from one sheet into another by index match. The formula has to index the information from either F1 IC / Recommendation, F2 IC / Recommendation or F3 IC / Recommendation by searching for/finding either Recommendation 1 ID No., Recommendation 2 ID No. or Recommendation 3 ID No. These columns are…
-
Count three things, two of which are from the same column
Hi, I’m hoping someone can help me on a formula I need in my calculation sheet that counts items from 2 columns within a different sheet. My formula was working until we decided to include more than one status in the count from the second column. Here’s the formula that was working until I needed to also include a second…
-
Field Service Management in Smartsheet
Has anyone built a Field Service Management solution in Smartsheet? I need to maintain a database of; store locations technicians, their backup tech and their backup of the backup tech dynamic schedule of availability of said technicians I envision some form of a 'lookup' worksheet where appropriate and relevant data from…
-
How can I add nested AVG in IFERROR
My current formula is: =IFERROR(AVG(COLLECT({Incident Duration}, {Month}, "February", {Calculations Range 3}, 2023)), " ") How can I add additional Months? I've tried: =IFERROR(AVG(COLLECT({Incident Duration}, {Month}, "February", {Calculations Range 3}, 2023))), " "), IFERROR(AVG(COLLECT({Incident Duration}, {Month},…
-
Having trouble returning valid dates
I am trying to track dates that are reliant upon a variable field. When the variable field is "x", the return in the second column should be the original date plus 1 year and 1 day. When the field is not "x", the return should be the original date plus 2 years and 1 day (edit for clarity: the first column should just be…
-
Tally of SOWs signed per month
Hello. I am trying to figure out a formula that will track the number of SOWs that have a status of "signed" per month (ie the number of SOWs signed in January, etc). This will eventually feed into a bar graph. I have the following formula so far and have no luck (INVALID DATA TYPE): =COUNTIFS([SOW, Change Order or High…
-
Avoiding Overbooking using Smartsheet Calendar/DataShuttle
Hello! I am trying to create a booking system for a room with multiple stations. Stations: 1 - 5. Time slots: Morning, Afternoon, All Day So far I have a dashboard with a calendar/form and separate sheet for the form submissions. With the calendar, people are able to see other bookings, but ideally, I want to use…
-
Formula for This week, Next week, etc.
Someone helped me with these, but I cant get them to work. Can someone let me know the error of my ways? This formula would be for last week =If(YEAR([Projected Start date]@row=YEAR(TODAY(-7)),IF(WEEKNUMBER[Projected Start date]@row=WEEKNUMBER(TODAY(-7)),1,""),"") This formula would be for this week =If(YEAR([Projected…