-
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…
-
Vlookup formula issue
Hi there- I had a formula entered into my worksheet that would auto-assign a colleague to order requests based on what groups they did ordering for. Somehow the formula was augmented so that it no longer worked. (i.e., I don't know what happened! I'm still learning.) I have the corresponding columns in the primary…
-
How can I identify multi-select cell values that are not in the dropdown list?
I have a multi select column "Cost Code." There are currently values in the column that are not within the dropdown list. For the "Accessory Code" I was able to check and flag these via the following formula where I reference a column in a separate =IF(CONTAINS([Accessory Type]@row, {A&A Column Selections Range 1}), 0, 1)…
