-
Fiscal Week Formula
Hello, I am looking for a formula to calculate the Fiscal Week duration of a project. The formula should be in the highlighted cells and should return the Fiscal weeks that have a value in the FW column. I am using this formula, but it returns Invalid Data Type =WEEKNUMBER([FW 37]1:[FW 52]1, 1, 0)
-
Convert Last, First Name to First Last Name
I have a contact list of Last Name, First Name; i.e. Doe, John. I would like to create a helper column with First Name Last Name; i.e. John Doe. Is there a formula to do that? Thanks in advance!
-
Looking for suggestions - master formula errors report
Hi all, looking for suggestions based on others' experiences. We have a series of dashboards and their respective back end sheets that occasionally throw formula errors for various reasons. I'm hoping I can leach some ideas from the Community on creating a master report/notification that informs us when any error occurs.…
-
Formula not returning correct data
Hello, I'm trying to count the number of projects by project owner and when it was completed. Below is my formula. It is returning data, but not the correct data. I'm sourcing from another Smartsheet. How can this be adjusted to work properly? =COUNTIFS({Completed Project Owner}, [Project Owner 2]1, {Completed Projects…
-
Index and Match
I am trying to use Index and Match in place of Vlookup and I find that when I am trying to update the reference all index and match formula's get updated to the most updated reference. Example: I am trying to pick up start and end date for one record. When I index and match reference to start, the end date also becomes the…
-
Manual Pivot of 400 unique items
I have a set raw set of data that is divided into 4 categories (i'll call it level a), and further divided into 400 (level b) more categories, each of which has a cost. Each item can repeat itself over an over, and the same item can belong to multiple categories of level a (but not level b) example set of data A B Cost 1 x…
-
Index/Match - Return the lowest row for multiple matches
I have an automation created that copies the row from the "Wetcast" sheet to a "Discard" sheet. From the Discard sheet, I want to return the "Ready to Ship Date" at the bottom most row into the "Previous Ready to Ship Date" cell in the Wetcast Sheet. The identifier/match value is the "ID" (GWC####). This is the formula…
-
Formula help - how to leave blank when Actual Finish is empty
Can someone help complete this formula? - looking for the 'Task Health' column to be empty when Actual Finish is blank, meaning task is not complete yet. Everything I've tried is either unparseable or results in a value. =IF([Actual Finish]@row > [Planned Finish]@row, "Late", IF([Actual Finish]@row = [Planned Finish]@row,…
-
Find all similar entry names and add their qty
Hello I have a sheet I made where I load in all my orders of items needed for a job. Each Item has a code assigned to it I'm calling the Entry Name. There will be times where the same Code/Entry Name will appear several times during the life of the project. For Example This week I might need 4 - BT01 (Entry Name) then as…
-
Can I do a conditional formatting based on values between two columns ?
I would like to do a conditional formatting on a column (column A) in such a way that if the value of "column A" is not equal to "column B" then the "column A" should be red. I dont see any options to compare values between two columns on conditional formatting. Your input is extremely appreciated.
