-
Date (mm/dd/yyyy) from Week number
I am trying to get the date displayed from a week number. For instance this week is the 27th week of the year. I am trying to get it to display 07/05/2021 (the first day in the week). I was thinking of converting the week to day of the year (i.e. 27 * 7 = 189) and using the 189 to convert to a date, but I can't figure out…
-
Cross-Referencing Assigned To Error
I am building out a roll-up sheet and have everything working nicely, with the exception of a single formula. The part of this sheet I'm having trouble with is in the image below. What I am trying to do is have this sheet look at the person named in the column "Primary", then check a couple other sheets to see how many…
-
Is there a way to conditionally copy the value of one cell to another?
I am trying to create a modified auto-numbering scheme where the numbering is based on a parent/child relationship. I think I am on the right track, but have hit a roadblock. My end result should be a calculated ID that is not overwritten when IDCalc changes. I would like the value of IDCalc to populate in the ID column…
-
How to automatically check a parent row when children are checked?
Hi, I have 3 level parent-children rows. I also have a column with a checkbox. Is there a formula or automation that would check the parent row if all children are checked?
-
Join-Distinct-Collect Formula Returning #INVALID DATA TYPE
I'm using a Join Distinct Collect formula to return a list of product numbers from a detailed schedule (formula below). I use this and other formulas to populate a weekly summary sheet. =JOIN(DISTINCT(COLLECT({Schedule Item}, {Schedule Make Date}, =Date@row, {Schedule Room Name}, =RmName@row)), ", ") Whenever the schedule…
-
HAS cross-sheet reference & Invalid Column error
I am trying to use HAS in a cross-sheet reference. I have a drop down form where the user selects a Strategic Priority and it goes into Sheet A below. In Sheet B, I need a formula to pull the text of the most recent selection. Here is the formula I am using in Sheet B, where Range 1 is the "Latest" column and Range 2 is…
-
Cross-Sheet formulas with Max Date
I'm a new user and trying to use a web content form widget to filter a report on a dashboard. I've got the form built and the form widget working on the dashboard. The form selections go to this Sheet A, where I have it set up that the box will be checked for the most recent selection. The report that I want to be filtered…
-
Formula to return a value if date is between entered date and calculated date
I have 2 sheets. On the first sheet I have a start date (entered value). From the start date I calculate the start date of the quarter of the entered date, and I also calculate the end date of the quarter 1 year later. All three were created as Date fields. As an example, if the date entered in 11/16/2023, the quarter…
-
Possible to automate numbers per row type?
I am adding a PO system in our smartsheet that holds different types of financial tracking. I want to auto populate new PO numbers, however, only for a row if a different column is marked as "PO Request." Is this possible? Currently the autonumbering feature does all rows. I've tried the following column formula so that if…
-
Why is my formula unparseable?
=IFERROR(COLLECT([Completed Date] - [LTP Conversion Date], AND(IFERROR(MONTH(@cell), 0) = Feb84, IFERROR(YEAR(@cell) = 2023, 0)), {CAD Support Type TD}, ="Long Term Project")), "N/A") The bold is what I want to subtract within the formula.
