-
INDEX & Match
I have what I am calling a 'Lookup' Worksheet. I also have a Change Order Log, an Invoice Log and a Contract Log. I would like the 'Lookup' worksheet to return all Change Order(s) info, Invoice(s) info and Contract info. Column 'PC #' is hidden and each of the columns to the right of 'PC #' contain Index & match functions.…
-
index and match formula that works in some places and not others
Hi, I have an index and match formula that works in some places and not others. I tried to attach a screen show that shows what I am trying to do along with this description. My formula in the Time and Attendance row works as expected. The data for overall status, scope, timeline, resources and funding are matched and…
-
How do I update my function syntax to stop getting the #INVALID DATA TYPE error?
I'm working within a sheet and trying to calculate the number of days between two dates. Above is a picture of what I'm seeing and my syntax is below. =NETWORKDAYS([Start Date]@row, [Date Completed]@row) I've tried to add IFERROR in a couple places and it didn't help with anything unfortunately. My Task Duration is set as…
-
Formula Help: Count number of time "Yes" occurs in a row?
Hi there! I am trying to write a formula that will count the number of times "Yes" appears across multiple columns in a single row. I am new to SmartSheet and having a hard time writing the formula. Can someone provide some guidance?
-
Advanced Formula #No Match Error
Hello, I have a master sheet that I am referencing which contains multiples rows that I need reflected on my teams sheet. There is a column named "Vuln Mgmt" (set as a dropdown/multi-select) that has options Yes: 3rd party, Yes: Security, or No. I want to create a formula that will reference the entire master sheet and…
-
Ignore blank cells in concatenation
I want to concatenate multiple columns data in a cell but would like to add a condition to ignore blank cells, I used a "|" to identify two different data's but if there is any blank cells "|" this symbol is keep adding without any data in between, =Product@row + " " + Variant@row + "|" + [Product 0]@row + " " + [Variant…
-
Nested If And Not Formula
Hi all, I'm working to write a formula that displays a health ball (R,Y,G,B) that's based on a variety of logical expressions. Below is what I am aiming to achieve. Status@row = Complete, then Green Status@row = On Hold, then Blue Finish@row = Blank, then Blank Here is where it gets tricky... If Finish is less than today…
-
Stuck on IF/AND formula
Hi I am using the Summary area and trying to create a formula that will show me when my Finish date is greater than today's date AND the status is not "complete" then say Overdue and if false say None Overdue. I have messed around with this for half a day (and other related items) and I am too frustrated to try anymore so…
-
Formulas- Equivalent of an array?
Hello! I am trying to write a formula that pulls information from one sheet into another based on a criteria. I have successfully written the formula to work for a single row, but can not get it to repeat for successive rows. I believe I'm looking for Smartsheet's equivalent to an array. I understand that a cell range is…
-
Purchase calculation from the grant amount
I'm maintaining a separate sheet to maintain the purchaser grant and I used index match formula to auto populate based on the purchaser grant ID, Now I want to minus total price from the grant and show the remaining grant when I do the second purchase with the same purchaser grant ID I should automatically get the…