-
Help with Formula Vlookup, collect, if?
Not sure if this is possible but I have a sheet to track monthly billing and another sheet to forecast milestone completions. On the monthly billing sheets I want it to look at the milestone forecasting sheet and if a milestone is forecasted to complete in a month add a billings to the billing sheet. Billing sheet looks…
-
How do you calculate an average on a column that also contains "#Invalid Data Type" in some cells??
I have a column of numbers (days duration) between two date columns but, in many cases, one of the dates may be missing which gives me an #Invalid Data Type entry in the column where I am averaging the days duration. Attempting to average the column, I don't know the syntax to skip over the #Invalid Data Type cells to only…
-
Count Year and Month Only from Separate Smartsheet
Hi all! New smartsheet user and learning formulas! I'm trying to count the number of jobs in a month per project type from a separate smartsheet (to eventually be turned into a graph). The date format in the original smartsheet or month/day/year. I just want to count the month + year. The Project Type is also in a separate…
-
Determine if items in multi-select dropdown match items in another list
I have a sheet 1 with a multi-select dropdown, eg, multiple names in one cell. I have another list of target names on a separate sheet 2, and I want to check which rows in sheet 1 contain the names on sheet 2, and how many names match in each row. So for example, if row 1 of sheet 1 contained the Names cell: Bob, Doug,…
-
IF(AND( Formula Calculating...half correctly
See above for reference. I cannot figure out what is wrong with my formula. My goal is that the Flag is "checked" anytime the second column of dates (named End Date/Ship Date) is within ten days or later of todays date, AND the status column is anything not matching Complete..... Completely brain dead after going at this…
-
Check room availability for same date.
Hello Smartfamily ! I have a sheet where I need to check if a room is available for the same date. The screenshots below shows how the column "Availability" SHOULD behave. ----- For the date April 24 2023, I select Room 1. Room 1 is not already selected for that date so "Availability"@row shows a checkmark As soon as I…
-
IF Formula with Multiple Choice
Hello! I want to build an IF formula into columns when the multiple boxes has selected the right department. IF the Authorizer is marketing and finance, the emails would automatically populate in marketing and finance column. Not sure if this can be done. Would appreciate your help greatly!! Thank you very much.
-
How to count number of projects in 2023 by current status
Hi, I created a Metrics sheet that feeds a dashboard last year that counted the status of a variety of items and it worked great when there was just one year. This year, I now need to only count the items that are for 2023. In the main sheet, I have a column that has the year in a string format (it's a formula…
-
Keyboard shortcut to delete a row
Hi Everyone, I am used to working in excel and using keyboard shortcuts to navigate my sheets without having to use a mouse. Is there a keyboard shortcut to delete a row? if not can how do I let the product team know to add this to the development queue? thanks!
-
JOIN DISTINCT COLLECT or JOIN INDEX COLLECT w/ Multi Dropdown List
Hi, I want to return the "Assigned to" contact for the "Assignee Role(s)" selected in a multi-select dropdown list. Here are the formulas I've tried so far: JOIN(DISTINCT(COLLECT({Project Settings - Team Members}, {Project Settings - Contact Role}, CONTAINS([Assignee Role]@row, @cell))), ", ")) JOIN(INDEX(COLLECT({Project…
