-
How do I display counts of all of the dropdown options used on one sheet and possibly multiple?
Long story short, I'm building a system to track pending legislation, and for each bill that posts, team members are assigned it to one (or more) categories in a CATEGORY dropdown column. I'm working on a dashboard of high-level views, and I'm trying to figure out the most efficient way to roll-up and display how many (and…
-
What formula to use to count all instances of a value across multiple columns.
Hello, My colleagues have set up a sheet to help them track sustainability actions in their department. They've asked me to help with setting up reports and a dashboard that summarise and calculates the types of actions across the 4 Action type columns, as well as summarising further by team. As you can see there's been an…
-
Is there a Formula to Insert Rows Based on Criteria?
I have 2 sheets, 1 that holds subsets of tasks, and a master sheet where work is assigned. If I want to add a subset of tasks to my master sheet based on criteria in another column, how would I achieve that? So if in my master sheet LP | Legacy Site Landing Page w/o Form is selected from the dropdown, the formula would…
-
MAX, COLLECT, MATCH Formula
I have a formula that is giving an "incorrect argument" error. =MAX(COLLECT({_Training Database Range 1}, MATCH([Primary Column]@row, {_Training Database Range 2}, 0), {_Training Database Range 3}, "ROVER - SITE CALIBRATION"), 0) I am trying to match a name for from a column (sheet 1) to a column in another sheet (sheet 2)…
-
How to use Countifs for rows that start with a certain character?
I am trying to use countifs formula. I want it to return how many there are that meet two criteria- the first being the first digit of a three digit number in one column is 1--. It would also have to be "Completed" in another column. =COUNTIFS({Number}, HAS(LEFT(@cell, 1)= "1:"), {Status}, "Completed")
-
Filter the Absolute Row Number
Hello, I have an odd request. Is there a way to have a filter so that it always shows the first 25 rows of a sheet and hides everything else below that number. I'm talking about the absolute row number. It needs to be a filter. We have essentially a list of tasks that are sorted by a number of criteria. I want my team to…
-
Merged: Changing Cell Value based on Cell with a Cross-Sheet Formula
This discussion has been merged.
-
Using COUNIFS to find a partial text match in 1 cell + if another cell is filled/blank/specific text
Hi SmartSheet Community, We've created a data set that is now being utilised by stakeholders, and we are trying to set up a way in which we can monitor the completion progress. The intention is to show each stakeholder their progress via our shared dashboard Description of our desired formula; Count if "X text" appears in…
-
Auto generate project number using formula
Hi, I'm trying to create a formula that will auto populate the project number when a new project is created through a form. When a project is added through a form it appears at the top of the sheet. Our project numbering system is YY-XXX where YY is the year of the start date of the project and XXX is the project number…
-
Refrencing cells and rows with formulas
Hello, I need to refrence cells from "Sheet 1" in "Sheet 2". I have made few columns in "Sheet 2" and in each of them, in the first row, I have the formula: =INDEX({Sheet 1 Range 1}; 1) Each column has it's own range, but I have to prefill the next 200 rows and I can't just write manually "; 2", "; 3" in each one. It's sad…