-
Sheet Summary not matching actual with multiple criteria
Hello, I am new to smartsheet formulas and have a problem that needs to be solved. I am trying to capture a count of sites that have 2 criteria. Completed in one column and counting if the dropdown is "Full Install" "Pre Cabled UPS Only" and "Revisit". On my sheet summary, I get a total of 14 using the below formula but my…
-
Why am I receiving #INVALID OPERATION error in some of my sheets
I have multiple sheets with the same columns (Health, Task Name, Start, Finish). Note that the Health column is a symbol (red, yellow, green, blue). In my summary sheet, I am using the following Index Match formula to search for Task A in Task Name column and spit out Health. =INDEX(Health:Health, MATCH("Task A", [Task…
-
Numerical "Priority" Method
Hello, I am in need of a method for assigning priority of a submitted row 1-10. If 1 is selected, the previous 1 needs to become a 2, the 2 becomes a 3, etc. The column needs to recalculate when a row is complete and the 'priority' column becomes blank. I don't think this would be possible without using many columns to…
-
ActiveCell References, COLUMN and ROW Functions
I want to make a sheet summary field return whatever is in row 6 of the activecell's column. It has to dynamically change with the cell's position. Similarly, based upon the activecell's row, I want to return the value of Column 2 of that row. Excel has a feature for ActiveCell which then you can use Offset or Index to…
-
Start date of the month for the previous 13 months rolling
I wanted to have a rolling 13 month table that and hoped to have a formula for the first date of the month for 13 months. I have a helper column with today's date in it and have listed the months and the start date as per the attached. Is there a formula that I can put in the start date column to have this roll on from one…
-
Deleting Duplicates by Column Formula
I have a sheet that has rows copied from another sheet. Updated versions of the rows are copied over, and I need to remove the old one. I have added two columns, Duplicate and Tag for Deletion. The formulas are Duplicate - =COUNTIF([Primary Column]$1:[Primary Column]@row, COLLECT([Primary Column]:[Primary Column], [Primary…
-
Index/Match vertical Report
Hello, I have a form for people to submit dinner menus for themselves and for up to 11 guests (but there would be 12 children as the primary contact meal would be a child as well), plus drinks. This forms feed into a sheet that has 74 potential columns of information. As you can imagine, it is too large to really see…
-
Formula to Capture Tasks Starting in the Next 7 Days
I need to capture the count of tasks starting in the next 7 days that are marked as Not Started. Currently anytime I change the Status to Not Started my formula grabs it. So the task will be in the past but the formula is still counting it. Below is the formula I have, I can't get my head around it, any help is welcome.…
-
Help with Join/Collect/Has formula
Hi there, I'm trying to use join collect to gather data that has certain info in the "collect" sheet. I have figured out the formula, but i need to put a space between items and i'm not sure how to do that. the formula i'm using is in blue above this is what it's returning, i would like a space between thing 1 and thing 2
-
Reference a date in a non date column
Hi SS peeps, I am trying to reference a date in a text column by using the date function but it's not working. =SUMIFS({Employee 1}, {Employee Date of Sep}, >[Jan 5th]45, {Title}, {Lead Sider}) I've tried using =SUMIFS({Employee 1}, {Employee Date of Sep}, >Date([Jan 5th]45), {Title}, {Lead Sider}) I'm basically wanting to…
