-
Formula for identifying duplicate data between cells in two columns and noticing blank cells
Hello! As the title suggests, I am trying to think of a Formula that will tell me if there is matching data in the two cells in question. I have a Helper Checkbox Column for this and this is generally the formula I'm concocting so far. =IF(ISBLANK(Office@row), 0, IF(COUNTIF(Office:Department, Office@row) > 1, 1)) This…
-
Search all of Column A, for the value listed in that Column C, Return Value in Column B
Hi All, I'm having a bit of an issue trying to figure out this formula in smartsheet. I have a sheet with the following columns: Facility Name (Column 1: This is a listing of all facilities we are contracted with) Client ID (Column 2: This is a unique identifying number for that facility) Facility Parent Name (Column 3: If…
-
Aggregation sheet
I manage a workflow spreadsheet that has a dashboard attached to it, the dashboard looks to find total revenue along with total outstanding number of pieces of work, and the oldest date of the outstanding work. For some reason the oldest outstanding reference formula I'm using on the aggregation sheet isn't working as…
-
Creating Formulas for Metrics
Hi Everyone! I am looking to get some metrics for a dashboard. I am trying to make a formula that will give me the amount of entries I have on my grid. based on the Line of Business and the Quarter. Both the Quarter and LOB columns are drop downs. Quarter has Q1, Q2, Q3 and Q4 while the LOB has Food & Beverage/Dining,…
-
Need Help Calculating % Change (increase or decrease) Between Two Numbers
Hi everyone -- I am new to Smartsheet and writing formulas and I've searched high and low in the community here to try to figure out how to calculate the % change, increase or decrease. I need to pull WoW numbers for a report to monitor the ups or downs in that %, but I'm having a difficult time figuring this out. I first…
-
Formula to enter date once a task is complete or criteria is met
Within our task list, we are trying to avoid additional work but having the date entered (in 'DATE COMPLETED' column) automatically once another field is either 'Complete' or 'N/A' ( Complete & N/A are 2 options from a dropdown list in another column. Thank you!
-
How to create a date to show the first date of the previous quarter
Hi, I am trying to find a formula that returns the first day of the previous quarter based on a date column (TODAY). Examples (DD/MM/YYYY): -if the date column is 02/01/2024, the formula should return 01/10/2023 -if date column is 31/06/2023, the formula should return 01/01/2023 Any help would be much appreciated. Thanks…
-
Cross Reference Report - Workaround Ideas
Hi, I know that I cannot cross reference a report; I'm hoping someone might have an idea on a workaround. I have a report that gathers data from all sheets in a specific folder. The quantity of sheets varies around +/- 200. I want to get one summary field (to match on) and the sheet link from each sheet into another sheet.…
-
Fiscal Month Formula
I have been upgraded from excel to Smart Sheet and most of my formulas are able to be translated into Smart Sheet. I am having an issue with taking a date that someone enters and converting into our fiscal. The fiscal months are the same as the calendar month but the month starts on the 22nd of the prior month. So for…
-
Help With Auto setting Fiscal Year based on Order Date
Good morning, I seem to be having trouble with setting my Fiscal Year on a sheet. It was working but stopped. This is what I am working with: I am importing a CSV file using Data Shuttle to import SKU information into an Import Result sheet. I am attempting to set a column up to display the Fiscal Year based on the date…