-
Flag if DATE A > DATE B
I'm working on a formula for a project tracker in my organization. The request is to flag a row if "Received Date" is greater than "Actual Date" for 3 separate dates - PO Received, Start Date, and End Date. I currently have a formula that works only if all dates are filled out: =IF(OR([PO Received Date]@row > [PO Needed by…
-
Copy and keep cell value based on day of the week.
Hello All, Is there a formula that would take a value from cell "A", copy it to cell "B", and keep that copied value in cell "B" until a set number of days have passed? Backstory: I currently have a dashboard with graphs that illustrates a rolling 7-day, 30-day, 60-day, and 90-day account interactions. I would like to…
-
Formula result from another formula
Hello, I need some help :) I need to calculate the [Taskl Name] and the [Status] from the next "milestone" in progress, and put those values in a header of the sheet. To determine the next "milestone" in status "in progress" i use the following formula, in every "milestone" row, on column: [WF-Multiplier]…
-
Form with formula INDEX/MATCH
Hello! I'm trying to see if this is possible. I'm creating a calibration form, but I'd like a specific task duration to be tied to each item, which then populates the due date. For example: Each piece of equipment has a set duration (ie. 365 days). This would be hidden on the form sheet. Once a user submits the form, I'd…
-
Average formula
Hi, Can you suggest me with a clever way of calculating average for a group of cell references? Please see attached screenshot of my sheet. Anytime the data for a new month is entered into my sheet, I need to manipuate the average 6 months formula so that calculates the average for the latest 6 monthly only. Is there any…
-
Expense Report Ideas
I am trying to build an expense report sheet for external attendees that come to trainings. Is there a way to allow them to submit each expenses separately through a form but then somehow automatically group rows together so that we are only paying each person one time for multiple expenses? These people will only attend…
-
How to count how many times a word appear in multiple cells?
I am trying to count how many times a word appear in multiple cells from a different sheet. I am using =SUM((LEN([Column6]2) - LEN(SUBSTITUTE([Column6]2, [Part Number]@row, ""))) / LEN([Part Number]@row)) and it works if I look just for one cell, but if I try to count from two cells appear Invalid type data. Anyone?
-
VLookup's in Smartsheet - Are there limitations?
Does anyone know if Smartsheet has a threshold limit on the number of VLookups that can be used within one sheet? I have 7 columns with a max of 300 rows of data in a Target sheet, I am wanting to run VLookup's with to 3 different Source sheets and I want to make sure there's not some crazy limit that says your formula…
-
How to reference cell based on MAX date and another condition?
Hello! I am trying to reference another sheet that has multiple data for the same building: On another sheet I am trying to pass over the information from Column4 if Column3 equals "Cobb Stadium" and by picked the MAX Created Date & Time. So it would pass "Yes" Since the last row has the Max date and time from today.…
-
Contents of a Dropdown Multi Select Column
Hi All I need help in using the contents of a Dropdown Multi Select Column. The column has the following options A B C I want to use the actual content say AB to lookup another Smartsheet for a price for that selection. I am sorry if there is an obvious answer as I can’t find it. Thanks AV