-
Generating dates based on dropdown choice in another cell
Hi everyone, I'm very new to this and trying to generate a formula that will calculate deadline dates based on dropdown choices in another cell. If we get customer queries by phone, we have a deadline of 2 days' resolution; if we get them by email, it's 10 days. I would like to be able to auto-generate deadline dates for…
-
Will the changing of linked cells not trigger automation?
I have two sheets with linked cells. In the sheet that receives the linked information I have two automations set up to change other cells on the sheet based on the linked information coming through, but nothing is happening. When I manually change the cells (overriding the links) the automation happens, but not when the…
-
Simple COUNTIF Formula Suddenly Stopped Working?!
Hi I have had a very simple countif formula, that has been working with no problems for months, suddenly start retuning a #INVALIDOPERATION error. I have reviewed the formula and can't see anything wrong with it, has anyone else had the same issue? Have Smartsheet released something and broken this? TIA
-
Linking from cell in another worksheet
Hi, I have a workspace consists of multiple worksheets. So far all the sheets' primary columns were exactly the same as they are all linked to one master sheet. Same Site Ids, all unique of course, same amount of rows,etc. For this particular example below, it appears that I need to add another PO#, but this PO number will…
-
Need to add calculation that includes possible leap year based on agreement date
I need to calculate the expiration date based off how many years an agreement is and need the date to end the same date as the agreement date. See columns below. I need to make sure the Option Period Expiration Date is the same month and day and the day seems to be wrong in the formula when there is a leap year and I can't…
-
Latest Date Before Today
Hi I'm trying to figure out if there is a formula that will return the latest date from a range as long as it is before today. To explain, I have a sheet that has a list of record of previous and planned visit dates per site. I want to be able to find when the last visit to a site was. I've tried using the MAX function,…
-
Divide By Zero Error
Hello, I was attempting to rid my sheet of the #DIVIDE BY ZERO ERROR and now I am getting the #INCORRECT ARGUMENT returned. What am I missing? #DIVIDE BY ZERO ERRO Formula =(AVG(COLLECT([PO Total]:[PO Total], [Order Date]:[Order Date], >=DATE(2021, 12, 1), [Order Date]:[Order Date], <=DATE(2022, 12, 31)))) #INCORRECT…
-
#NO MATCH error
Hey everyone! I seemed to have ran into an issue using a vlookup formula. When I first wrote it out it worked for that cell but when I dragged it down the whole column it no longer worked. =IF(VLOOKUP([H145 A/C #]@row, {QG Schedule Tracker Range 4}, 2) = "Not in SS", "No", "Yes") This checks if the aircraft number in that…
-
I would like to return all Addresses from another sheet where Column [Status] Matches a criteria
I would like to return all Addresses from another sheet where Column [Status] matches certain "text" I have tried two formulas and at present they only return the first instance of the address, I have generated a report, however i now need to send this new sheet on to a team member to ensure the follow up work is carried…
-
CONTAINS query - Returning a word from text string & validating it is in a specific format
Hi All, I have a column [TEXT] In it may be something like the below in random order - word1 word2 PID#XXXXXX; word3 word4 PID#XXXXXX; word1 word2 word3 word4 word1 word2 word3 word4 PID#XXXXXX; I would like to extract the entire word that begins with PID# into a new column [PID] So that [PID] would just display…
