-
Trying to checkmark an entire column based on an absolute cell reference. Help pretty please.
Hey there, So, I'm trying to automate a process where if a new row comes in (through a form) with a checkbox "checked" (in column Open ECO? row 1 only) that the check marks in Column 18 will all be checked. Any thoughts? thanks in advance, -ryan
-
Linking numerous cells from one sheet to another (GC Bids)
I have a source sheet that has 18 parent rows. There are many child rows per parent. I have helper columns to analyze the data in the source sheet. I have created SUM(CHILDREN()) on each parent row. I now need another sheet to perform calcs on each of the parent rows, however, this second sheet (destination sheet) does not…
-
Count Rows in Sheet Meeting Certain Criteria
I'm trying to get a count of the number of rows in my sheet that meet a certain criteria and am getting an #INVALID OPERATION error. Here is what I have so far: =COUNTIF([Resident (Lookup)]:[Resident (Lookup)], AND([Resident (Lookup)]@row, Status:Status <> "Closed")) What I'm trying to do is get a count of the number of…
-
My Syntax is correct but I get invalid refernece
The following formula appears correct in that I want to the correct response only if the UPN column is populated and the Construction column cell 1 is Greenfield, one answer if construction column cell 2 is Brownfield then index the exists column to see if the closest parent already exists. if so return "existing" if not…
-
Status Column Formula
Hello Team, can you help me with status column formula to automate status column: Green = "% complete"=0% Blue = "% complete"= 100% Yellow = "Finish Date" >Today() but < Today () + 3d Red = "Finish Date" + 3d Remaining "Green" I was trying but unparseable: =IF([% Complete]@row="100%", "Blue", IF[% Complete]@row="0%",…
-
INDEX/MATCH or VLOOKUP for Date Range
Hi, 1) I have sheet "X" set up as shown in screenshot 1 below where I have got a start date and end date column and the week numbers associated for the date ranges. 2) I have got another sheet "Y" set up with empty week number column as shown in screenshot 2. Please suggest me with a formula to enter in the Week number…
-
Convert Cells into a Formula Columns Issues
Hello, I'm getting the below message when I'm trying to convert a cell into a formula. The sheet will receive its data from a form, except for the cells I'm converting. The column formula syntax isn't quite right, see our help article https://help.smartsheet.com/articles/2481944?ss_lc=en_US&source=apphelpicon for more…
-
IF(AND Invalid Operation Error
Hi! I need help fixing this formula - keep receiving an Invalid Operation error. I'm trying to return "No" if an email from the row in the Smartsheet file matches an e-mail from the cross-sheet reference's file in the E-mail column and that cross-sheet reference's row also contains "Mgmt" in the User Role column. (So two…
-
Counting help
Hi There! I am trying to create a reference sheet to pull data for a dashboard and am trying to figure out how to count instances a item with a certain value is check off. So, how many items checked off of the list are for "Property Info". I've tried using COUNTIF, COUNTIFS, COUNTM and so on, but I can't seem to figure…
-
Unable to add up duration for metrics calculation
I am trying to use cross-sheet formula to add up the planned duration for certain workers, for performance analysis purpose. After setting up the Actual Start and End date, we copied it to the Planned duration, Plan start, Plan end - as a baseline reference. When I am trying to calculate the Planned days by employee, it…