-
Basic NETDAYS Help!
Disclaimer: I'm a SmartSheet beginner, and only at an amateur level with Excel formulas. I've reviewed quite a few NETDAYS posts on here already and tried variations of the solutions in all, but so far every NETDAYS variation I've tried has returned the #UNPARSEABLE error. I'm trying to write a formula that will calculate…
-
INDEX(MATCH) or JOIN(COLLECT)?
When we first started using Smartsheet, the rep who handled our "Launch Package" created the following formula. =IFERROR(IFERROR(INDEX({SupplierItemNumber-AL}, MATCH($[Our Part Number]@row, {OurPN-AL}, 0)), INDEX({SupplierItemNumber-MZ}, MATCH($[Our Part Number]@row, {OurPN-MZ}, 0))), "ERROR - CHECK PART#") It searches two…
-
Formula for return X value if there is ANY text in a cell, otherwise return text in another cell
Hi! I am working on a formula for a sheet that has a product column, a Use Cases column and a Tool column. I'm hoping to use a formula that says, "Look in the Use Cases column for any text. If you find text, return a "Use Case" in the Product column. If you can't find any words, then return the value in the Tool column in…
-
SUMIFS with Data in another sheet
Hello - I need help with the following. I created a summary sheet with goals by Functional Area (Department). I want to add 2023 actuals (to compare the actuals with the 2023 targets). The actuals information is stored in another sheet. I am thinking about using SUMIFS. Here are my 3 pieces of information: The range to sum…
-
Calculate Total % Complete Checked Boxes of all Parent and Child Tasks
Project: I have a massive checklist we use for event planning (700+ rows!) and want to be able to know how far along in the planning we are percentage wise for one particular event. Goal: I want the Parent row %s to be the total percentage complete of all their Children tasks. The parent row is setup as an event planning…
-
Excluding and Including data in a countifs formula
I need the total number of that meets the following criteria - Banner Name, Open, and not MA or NY. I am able to write the countifs to include the specific banner name and whether the status of that site is open, but I can't figure out how to exclude certain states or include certain states. This is what I tried below…
-
Percentage of "Yes" Values in a Month
I have a sheet that includes a column (named "PTP") of Yes/No data. I need a sheet summary formula that will calculate the percentage of "Yes" answers in that column, across rows that fall within a calendar month, per a date in a different column (named "Actual Start"). Said another way, I need this formula to search…
-
Parent Child Numbering
With A LOT of help from the community I was able put this together but, I think there must be a better way. The last column is what I am trying to get to. Auto-generated Sequential number in this format T-00001 when Ancestors = 0 Auto-Generated Sequential number in this format T-00001.1, T-00001.2 (where T-00001 is the…
-
Return Text from the Left of "("
Hello, I need to return a 11 characters from the left of a parenthesis from a string of text. For example T0000000001 T0000000002 T0000000003 (Primary Case) I need the formula to return only the 11 characters from the left of "(Primary Case)" T000000003
-
Formula to extract a portion of a name
Hello,, In Excel I would use text to columns to do what I'm trying to accomplish here but I would like to try a different approach. I'm really close but seem to have hit an impasse. I have a name column that is formatted thusly Lastname, Firstname MI. where some employees do not have a middle initial. I want a column with…
