-
If/And/ISDate/OR
Hi All I am trying to set up a status message based on a few variables. I can get them to work indidually, but when I bring them all together I get an InvalidData error. The parameters are If FWA Required= YES and FWA attestation is Date and Date Attestation Form Signed is Date and Education Completion Date is Date. If all…
-
BOM - INDEX/COLLECT Formula referencing child rows only in another sheet
Hey there, If anyone can help. I have set up two calculation sheets. The first is a Bill of Materials with the second being a variations sheet where I can combine various options from the BOM sheet. Each BOM item has a corresponding unique ID number. In the Variations Sheet I want to have a list of the BO so I can easily…
-
COUNTIFS, CROSS SHEET, HAS, MULTI-SELECT DROP DOWNS AND MULTIPLE CRITERIA
I feel like I've been going crazy. Trying to get a count of how many [Primary Column]45 appear when the other ranges have the listed criteria. All ranges are multi-select drop downs. Keep getting 0, where I know there is at least 2. =COUNTIFS({Rx Consumer Complaints Tracker Range 1}, HAS(@cell, $[Primary Column]$45), {Rx…
-
Formula to Refresh ONLY if Status is Open
Hi all! First of all, thanks for your assistance. I have a sheet, and I want to create a formula to another (a simple VLOOKUP). However on my initial sheet where the formula resides it will be based on the Status. If the status is Open, I would like the formula to make any updates. However if the record has a Closed…
-
IfError- should this work?
I Am trying to get an IfError formula to work and not having any luck. CAn anyone help? =IFERROR([Product Type]@row = "Hardware", [Actual Shipment Date]@row >TODAY(-12), [Backlog @ Bud]@row), " ") I want that if a row is hardware and the actual ship date is not within the last 12days it populates the cell with the value…
-
Sum Child Rows when "children" are a different column
I am sure this has been asked before, but I can't seem to figure out a solution that will work in my sheet. I am trying to sum the child rows of a specific column, but my formula isn't working. Can someone take a look at the screenshot and see if you can help? I need to add the individual expenses (in the Expense Total…
-
Concatenate Name if Preferred Name not blank
Hi, I am trying to create a formula where I can concatenate a person's name as "Smith, John" if they don't have a preferred name listed (if blank) and as "Smith, Jane (John)" if they do have a preferred name. My fields are Last Name, First Name, and Preferred Name. Can anyone help? Thanks so much!!!
-
COUNT(DISTINCT(COLLECT - Counting a value per employee
Hi, I'm trying to count how many values I have for certain people. I have two values that are assigned to the employees daily, for each day we have a value of either "Y" or "N". I want to count how many "Y" an employee has. I'm getting this information from a big database, to have it summarized in a small one. I've tried a…
-
How to autopopulate % Complete based off of other cells entry
Hello, I am trying to autopopulate % complete based off of other cells YES/NO entries in the sheet. I have 5 criteria to determine if we get to 100% Completion or not: Columns: Paperwork Received - YES (add 20%) NO (add 0%) Samples Received - - YES (add 20%) NO (add 0%) HPCL Received - YES (add 20%) NO (add 0%) Hold…
-
Status Ball change 30 days after start date
Hello! I am assisting a colleague with a smartsheet issue. She would like the status ball to become yellow when 30 days have past the RFT start date and red when 60 days have past the RFT start date. I can't seem to figure it out and I thought I'd see if anyone can help while I continue to try and figure this out. Thanks…
