-
Update parent value if all children are a specific value, leave alone otherwise
I'd like to update the main parent row cell to "Complete" if all the children are also set to "Complete". Otherwise, I'd like the parent row cell to just be left alone (it will be blank until everything else is "Complete)". I can make this happen based on the other statuses the children show by using a bunch of COUNTIFS…
-
Formula for state abbreviation and zip codes into time zone
I've been trying to create a column formula for two scenerios. One to turn state abbreviations into time zones in a seperate column. Then turn zip codes into time zones in a seperate column. I've tried using this formula for turning states into time zones without any luck. Can you advise? =IF(ISNUMBER(SEARCH((Applicant…
-
Copy rows within a sheet using automation
I have been using a helper sheet and automation to accomplish this. However, a problem occurs when you have a column that is an auto number and need a new number created. Copying from sheet A to sheet B keeps the auto number value, but copying/pasting a row within a sheet assigns a new value -- it's inconsistent behavior.…
-
SUMIFS Statements with less then percentage
Hello! I am looking for some help with my Smartsheet's formula below: =SUMIFS({GDP Project Tracker Range 1}, {GDP Project Tracker Range 2}, "1", {GDP Project Tracker Range 4}, "<=2", {GDP Project Tracker Range 3}, "Active") + SUMIFS({GDP Project Tracker Range 1}, {GDP Project Tracker Range 2}, "1", {GDP Project Tracker…
-
Count Unique Assigned to values with a Specific Status
I have a COUNTIFS formula that I use in a roll-up sheet to count the number of tasks specific individuals have assigned to them on a Project Task Sheet. The values from the roll-up sheet are fed to a dashboard. =COUNTIFS({Assigned To}, Primary9, {Status}, <>"Complete") The problem with this formula, is that it does not…
-
Is the a Cert Prep Template?
Is there a template that currently exists that is designed for Certification Prep? Something that would allow me to see HOW things worked, and then I could create myself. I am a visual person, so seeing a formula based on my sheets is how I understand how it works, as opposed to the standard syntax of a formula. I know the…
-
Countifs between two dates, multiple sheets
Sheet 1 has a column "Date Conducted". Another column "Status" with dropdown options "Safe" and "Improve" Summary Sheet has two columns, "Start Date" and "End Date". On the summary sheet, I'm trying to count the number of "Improve" where the "Date Conducted" is between the "Start Date" and "End Date". Any ideas? Thanks!
-
Formula double-counting
Hello, hoping someone can help me rewrite the formula below: =COUNTIFS({Certification Name-Primary}, "NSE2", {Reviewed&Approved}, true, {At risk check}, true, {Expiring-Expired}, true) Right now it's only counting if all things are true but what I want is for the count to be limited to if the "at risk check" OR…
-
Add Source Sheet When Copying Rows to New Sheet
Is it possible to put a link to the source sheet that a row was copied from in the copied to sheet with a formula so it adds the source each time? Sort of like a report will give the source sheet. I'm copying rows to a rollup sheet, but I'd like the roll up sheet to have the link so it is easy for the used to go to the…
-
SUMIFS - Stumped on calculation to figure out if within a month
Hi All, I am using SUMIFS to calculate if a part is in the Spend Formulas Range sheet and then if that same part has a Date that is within the month of January. The formula seems to break when the dates come in. =SUMIFS({2023 Spend Formulas Range 1}, {2023 Spend Formulas Range 2}, [Original Material #]@row,…