-
Date Overlapping Formula with Same Resources
Hi Smarties, I am working on a challenge below hoping for some help. I would like to raise a flag (on a flag column), if Start Date and End Date of a project conflict with other Start Date and End Date. Projects are the ones with Header "0" Only if the Start Date and End Date of the projects are assigned to the same…
-
Summing the multiplication of two columns
Hello, I would like to know how to use a formula to multiply two columns and sum the results over multiple rows. My columns are "salary" and "percent_work" (that is, 1.0 = full time, 0.5 = half time, etc.). My rows are the employees. I need to multiply salary * percent_work and sum over all employees to obtain the total…
-
COUNTIFS for cell value between 2 limits
Hi everyone, I'm using the appended formula to count the number of times the cell values in a column fall between two limits. =COUNTIFS({Summary sheet Family}, "Cups", {summary sheet install phase}, "Storage", {summary sheet Storage duration}, >=[Metric1,]26 < [Metric2]26) The above formula works without error, but does…
-
Cell History Changing Due to Time Zone for Column Formula?
Hi, I have a column level formula and the oddest thing is happening. My cell value keeps changing to a swapping date when the actual cell containing the date has not changed (cell history checked). I'm hunching that a user is using different timezone date format which is why the dates flip between MM/DD and DD/MM and back…
-
Forms or Formulas with appointment scheduling
Hello All! hope you can assist. Trying to have a form (external users fill out) to book a time slot - Once that time is gone - no longer available to anyone else and thus is removed from the options. Is there a solution, formula or best way to achieve this without using a third party booking tool? 🤔 Thanks Trish😀
-
Count cells from another sheet <> to a value or is blank
I am trying to count cells from another sheet that do not equal certain values OR are blank. When I create a filter on that sheet, I find 40 cells that fit the criteria. However, my formula from my Metrics sheet counts 0 cells. COUNTIFS({Subtype}, <>"Electronic Messaging", {Subtype}, <>"Core Sales Aid / Visual Aid",…
-
cut and paste referencing
Hi everyone, It seems Smartsheets uses absolute cell reverencing and consequently cells with formulas lose the cell references when cells/columns are are moved/changed Is it possible to use relative referencing to avoid formulas losing their cell reference? thanks for any advice Tim
-
Copy a date to a cell only if there is not already a date there
I'm trying to capture a date from a cell to another cell only if the current cell is blank Here is what I have so far: =IF(ISBLANK([Proposed PROD Release Date]@row), "", IF(ISDATE([Proposed PROD Release Date]@row), [Proposed PROD Release Date]@row)) This formula is in [New date] cell but I only want this to occur if new…
-
Counting Incomplete Past Due Activities by Category
Hi all, I'd like to get a total count of the activities in my project plan which meet the following criteria: 1) "Expected Due Date" is past due (<= Today) 2) "Expected Due Date" is not blank 3) "Done?" is unchecked 4) "Category" is "ABC" I assume I'll need a check box column with a formula to indicate if these criteria…
-
VLOOKUP returning "incorrect argument set" error
BRAND NEW TO SMARTSHEETS HERE: I have two sheets which have a unique ID column in common as a key. Sheet 1 will have only a single time this key will be listed. Sheet 2 could have many entries that have the same ID value displayed multiple times. I am trying to pull a field from sheet 1 into sheet 2. I want column 6 from…
