-
Sheet reference Countifs formula with a date
Hello, I have a column that's a date and need a formula on a separate sheet to count if the date is current year. I haven't been able to find any community posts about sheet references, only cross sheet ones. Current formula: =COUNTIF({Date Column}, ISDATE(>2023)). I get an invalid ref error.
-
Help on formula: format numbers and shorten to K (thousand), M (million), B (billion)
Hi all, So I was able to get help around here on how I could turn amounts in thousands and millions to just K and M instead of displaying lots of zeros. Now I would like to add a formula in to just round it up with just 2 decimal places and to shorten billions too. Can someone help please? Here's my current formula:…
-
Formula to Determine Numeric and Non-Numeric Values
Hi all, I am trying to combine a formula converting a numeric value to a specific format with a check for non-numeric values so the non-numeric values are passed through and don't cause an error. I was able to account for specific non-numeric values however, we have some variability I need to account for. Formula for…
-
I need help with a formula with 3 range references from another sheet
I have 2 sheets, a source sheet and an update sheet and need help updating a cell when 3 criteria are met from the source. Employee ID = Employee ID (Range 2) Date = "11/6/2023" in source sheet (same row) (Range 3) A calculated field on source sheet = 1 (Range 4) The third criteria is new so this formula works today:…
-
Why would a formula work on one sheet but be #unparseable on another?
Hello All, I am trying to use a formula I use on one of my sheets to collect data. It works great, but I made a new sheet to capture the data as the other one is messy as I was still learning. My Formula works great on the original sheet, but when I use the same formula on my new sheet it comes back #unparseable. This is…
-
Formula to Match cross sheet and COUNT instances.
Hello Smartsheet Community! I'm trying to create a formula that generates an integer value based on two criteria. I have one sheet that acts as a repository for all of the daily activity at multiple sites, for each day of the year. My formula needs to MATCH [Date@row} to the cross sheet reference range {Master Shipment Log…
-
Issues with TODAY() and daily reporting
Hi there. In part of my duties, I run a report that shows activities entered on a form from yesterday. We do achieve "yesterday" by using the following formula: =IF(DATEONLY(Created@row) = DATEONLY(TODAY()) - 1, "Yesterday", "Not Yesterday") In using this formula we have noticed some alarming discrepancies. Reports with…
-
Copy a date in a cell that is formated as a date
Hello everyone, i have a layout where project managers fill in all relevant information into an intake sheet. And then i have created some formulas and automation so that the project sheets would fill themselves upon creation. So far so good. Now I have different date columns in the project sheet that need to be filed in…
-
How to display numbers in billion in a cell and that is linked from another cell with currency
This is my current formula for million but I need in billion. Please help thanks ="EGP " + LEFT([Total 2023]57, IF(MOD(LEN([Total 2023]57), 3) = 0, 3, MOD(LEN([Total 2023]57), 3))) + IF(LEN([Total 2023]57) > 3, "," + MID([Total 2023]57, IF(MOD(LEN([Total 2023]57), 3) = 0, 3, MOD(LEN([Total 2023]57), 3)) + 1, 3)) +…
-
INDEX (COLLECT) - From two separate source sheets
We have a process where live jobs are created on a master sheet then moved to an archive sheet when finished. We have a number of other sheets which use VLookUps or INDEX(COLLECT) to pull required information in to another sheet. How can I create a formula which will look at sheet 1 for the Job No and pull through the…
