-
Metric Sheet Formula
Hello - I am trying to reference multiple columns in another sheet for a formula, but the columns are not next to each other in the sheet that I am referencing, is there a way to reference columns not next to each other? In the end, what I am trying to do is get a count of how many times this person's name appears in the…
-
Tracking "Time at Current Stage"
Hi Guys I have a question regards tracking comments/ current stage of ticket items We have an export that is added via the Data Uploader which contains "Last Week" & "This Week" fields. We are hoping to expand the range by which we can track how long an item has been at a certain stage. The idea is we add the file via the…
-
Formulas
Hi there, I need a hand - I would like the symbols to change colour according to a date in the 'Expiry Date' Column. Red - Todays date or less Yellow - Less than 90 days from date in column Green - More than 90 days from date column If possible when the symbols change colour the assigned person be emailed. Thanks again!
-
vLOOKUP on Multiple Dropdowns Pulling, Calculating Totals
Hoping someone can help! I am have created a multiple dropdown list in one column (a), and a VLOOKUP function using this data in a separate column (b) to pull through pricing from another sheet. I am having some issues when column A has more than one option selected. How can I create a formula that checks all options…
-
Calculating a due date based on date received in hours vs days
Hello! I am intaking orders from my clients. A date, with hours and minutes, automatically records when a row is entered. I would like to set up a column for a due date that uses the same date/hours/minutes display. Example: A row is entered and a date with hours and minutes is automatically recorded. An express order is…
-
Convert Text to Date Format
I am trying to copy a text column written out as "Thursday 07/29/2021" into another cell with a date format "07/29/21" in order to use that cell as a reference. I have tried the VALUE formula but have not been able to get it to work... Thanks for you help!
-
Index and Match Formula
Hi! This formula returns unparseable (formula is based off what I did in excel): =INDEX([Phase]1:[Comments]57,MATCH(0,[Date Completed]3:[Date Completed]57,-1)+1,MATCH("Phase",[Phase]1:[Comments]1,0)) There are multiple rows of tasks. I want to get the phase that has a date started but not completed, up to the very top row.…
-
Inserting Quarter and Year for fiscal year (July-June)
Tried to use other formulas on here, but can't seem to get it to work. Our fiscal year is June - July. Trying to have a column show Q1 - FY22 if a date referenced is July - Sept 2021, Q2 - FY22 if a date referenced is Oct - Dec 2021, etc. The column I want to read against is "Completed Date" and both columns (Completed…
-
Sum Ifs formula to calculate everything in a certain stage each month IF that month is in the future
Hi. So this is the formula I currently have: =SUMIFS([month #]66:[month #]77, >=MONTH(TODAY()), Client66:Client77, Stage:Stage, "Stage 1 Prospecting") The month # is 1-12 on each row for each monthly calculation of TCV , and then the Client is the actual monthly calculation. I am getting an invalid operation error. What I…
-
Apostrophe in formula
Aim: I am running a helpdesk in Smartsheet and am trying to autopopulate 'ticket closed' fields for Year, Month, and Week according to a 'date closed' field. E.g. YYYY column: =IFERROR(YEAR([Date Closed]@row), "TBC"). Once a ticket is closed, the YYYY cell should display '2021'. Context: The sheet is a local sheet (for a…