-
Two forms, one sheet moving responses to new cells
I have one sheet with two forms. It's for employee onboarding. One form records some information from the employee and one form records information from the hiring manager. Two new rows are created in the sheet once the forms are complete. I'm trying to link those form responses to other cells within the same sheet (in a…
-
How can I create an inventory checkout form that shows availability of equipment using barcodes?
Hi Smartsheet Gurus! I want to create a library style system where staff can request equipment through a form and know instantly if the item is available. I think this can be done using the barcode inventory management template, but I don't know how to link the barcodes to the forms. Any tips/formulas/guidance from the…
-
Health Column taking Predecessors Column into consideration to determine color.
I have the following formula in the health column. =IF(OR(Status@row = "Completed", [End Date]@row - TODAY() >= 7), "Green", IF([End Date]@row - TODAY() <= -1, "Red", "Yellow")) It works fine. If End date is more than 7 days out then the health column is green, If End Date has passed then Red. The problem is that I didn't…
-
"Locking" an Index/Match formula
Hi, I am using an index/match column formula to look up an approver from another sheet. This is working fine, but I am going to need to change an approver and want to keep the previous approver listed for the past records. Does anyone have any suggestions on how to do this? I would rather not create a new column or copy…
-
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…
