-
Best method for linking 2 sets of data together that use forms to collate the information?
I'm working on a project to help devise a solution to track all of our organisation's skills & talent initiatives, plus that will provide us with some useful metrics. The plan is have 2 forms - part 1 that collates all of the schemes' information and then a part 2 that collates information on outputs, but which is sent…
-
Reverse the Ranking Order
I am working on ranking clients based on revenue with the highest revenue being number 1. In the end I want to build a top 25 list that could change on any given day based on new data. I used this formula to rank my items: =RANKEQ([Lost Client-SUM-BI Estimated Annual Revenue]@row, [Lost Client-SUM-BI Estimated Annual…
-
SUMIF and NOT Functions
I am working with three formulas to collect data in the Summary. I want to add the NOT Function to these formulas to exclude the rows that are starred in the CCC column. =SUMIF({Region}, "Philadelphia", {Contracted}) =SUMIF({Region}, "Philadelphia", {07/09/25}) =SUMIF({Region}, "Philadelphia", {Difference}) Thank you
-
COUNTIF Formula in Multiple Dropdown Box but Exclude a Word
I’m trying to create a COUNTIF from a multiple drop down column (referencing another sheet). Two words are similar: Active and Inactive. If I use CONTAINS, it counts both Active and Inactive: =COUNTIF({Certified Roster Range 1}, CONTAINS("Active", @cell)) If I use HAS, it counts when Active is the ONLY item listed in the…
-
Automate Date Fields
I have reoccurring tasks that have start dates and end dates. What I would like to do is after the status changes to completed, then automatically change the date fields to the next occurrence. For example: I have a task that has a start date of 7/1/25 and an end date of 7/10/25, and that task reoccurs quarterly. Is there…
-
Top 10 chart based on most entries
Hi everyone - I'm wanting to create a dynamic top 10 list for a few metrics on a sheet. In summary, we have an issues log. I want to be able to chart the top 10 customers with the most complaints added to the log. As well as the top 10 reasons for the compaints, etc…. Similar to what i would do with a pivot table in excel.…
-
=IF(AND(OR(HAS Column Formula with 3 results (option 1, option 2, both) #INCORRECT ARGUMENT SET
I have a search range which is a column that has up to 35 bus routes. The bus routes are operated out of one of two facilities, "ARCADIA" and "POMONA". I am trying to create a column formula that will return a result of "ARCADIA", "POMONA", or "BOTH". I can successfully create a column formula that will return one of those…
-
Sheet Formula to reference DataTable
DataTables work well for their limited purpose. However, to analyze the data you must create a limited sheets from the data then analyze that data, even if all you want is a Sum Total. I would be great to be able to use formulas that reference this data, such as: Sumif(s), Countif(s), Index/Match and others.
-
Summary Sheet & Filter based on Form Responses with Multi-Select Dropdown
Hi all! Hoping for some assistance with a summary formula pulling in criteria from a multi-select dropdown response form... I have a dashboard that has a form embedded in it to allow the user to enter their desired criteria (see below screenshot) and then the bottom of the dashboard pulls in the metrics using the form…
-
Average of Column
Hello! I was wondering if anyone knew of a way to get an average of a column. I know we can get an average of a specific range in a column, however this sheet I'm working on is a survey and there will be ongoing entries. If possible I just want to get the average of the whole column so I don't have to come back and update.…