-
Combine Cross-Referenced Sheets
Hi - I have 6 Summary sheets that are cross referenced to 6 Regional sheets. These have formulas gathering totals by person. Sort of mimics a pivot table. Works great for stacked bar charts. I'd like to combine all the totals for each person in one sheet. People might appear on more than one Summary sheet. The names may…
-
CHALLENGE: Unpivot financial data (reverse pivot)
I have a challenge for this awesome community.... has anyone successfully found a way to unpivot data? I need to take columnar data ($ per month with each month in a column) to become row data (each project and month on a row). I need to do this in order to mesh user-entered horizontal forecasts, with Oracle-sourced…
-
Using JOIN COLLECT or INDEX COLLECT
I am using two Smartsheets and pulling in information from one sheet to another sheet through an INDEX formula - =INDEX({Live Lesson Reviews (Quality Assurance) Range TPD}, MATCH([Active Teachers]@row, {Live Lesson Reviews (Quality Assurance) Range TN}, 0)) Sheet 1: Live Lesson Reviews (Quality Assurance) contains two…
-
Using IFERROR to reference 2 sheets
Using the formula below to pull references from two sheets. I am getting data returned from the first sheet, but not the second. When I reverse the order of the references the formula works the other way around. Do I have an extra bracket or something causing it not to index past the first sheet? =IFERROR(INDEX({Milestone…
-
Form inputs data to sheet, create a trigger to use some data to create a form?
Question is a bit vague in the subject. I do hope it is enough for others to search in the case this is possible or even answered. Scenario: Working on a procurement request process. Users put in a request, based off an item sheet (budget with items listed). Once a specific item number is selected, currently it will…
-
Combining Formulas - Average, Collect, Combine, Value, IF Error
Hello, I need help with a formula combing average, value, collect, and contains. I am trying to pull data from another sheet pulling out just the values from the selected options (options are 4 - Complete Success, 3 - Success with Minor Risk, 2 - Success with Major Risk and 1 - Process Breakdown) and then to average them…
-
Subtraction Formula
Hi folks, I have a Debt total in a cell on my budget spreadsheet (e.g. 7000); I have a row of payments by month (e.g. 160). I want to subtract each new cell of payment of 160 from the total cell of 7000. I want to visibly see the reduction on the 7000 debt cell when each new month of payment is added. Is there a formula…
-
Date Formulas based off Created column calculating from / converting to GMT
Hi all, I've just discovered that date formulas pulling from the Created column will convert the date and time to the equivalent Greenwich Mean Time date and time. For our central time zone office, any submissions that occur at 7:00pm or later will show a formula-driven date of 12:00am GMT, which is a day after the Created…
-
Need help - adding less than or equal value to a current formula
Hello, Currently I'm struggling to add <= value to my current formula code, my idea is to count for Order Numbers (Unique values) and Lines of Order Numbers of certain times. since filter doesn't work in this code, I'd like to add a <= or >= value formula for Ship_Time, so my code filters can automatically generate…
-
Count Distinct Collect Find not calculating correctly
I have a formula, =COUNT(DISTINCT(COLLECT({CW Number}, {Opportunity Type}, FIND(Category@row, @cell) > 0))), which is not calculating correctly. See image below. For the Volunteer Type of Tutoring it lists 8 but it should be 9. All other calculations using this formula calculate at 1 or 0. The totals should be much higher.…
