-
How can I copy information to a new sheet to create a log?
I'm trying to see if there's a way to have data copied from a few specific columns to a new sheet. I'm hoping to create a log for IT issues as they occur on requests. The difficulty is setting some sort of trigger that will move the information over into a new row whenever a new issue is added. The other possibility, would…
-
Formula for Star Rating that Ignores Blank Cells and O Values
I would like to Average the Star Ratings provided by seven people in the Rate Column. The formula I have in the Rate Column is NOT ignoring blank cells or cells with a 0 value. The image below shows the hidden columns that are connected to the formula. =AVG([DH#]@row, [JH#]@row, [RH#]@row, [JM#]@row, [RF#]@row, [KM#]@row,…
-
SumIFS with YTD and Month
Hello, I am trying to summarize the total of cures by branch, by year and month below is the formula I am trying to use. SUMIFS({Cure Amount}, {Date}, IFERROR(YEAR(@cell), 0) = 2021, IFERROR(MONTH(@cell), 0) = 1, {Branch}, "Triad (800)") Your help is greatly appreciate. Thank you
-
#Invalid Data Type for identical formula on one sheet that works on another sheet
Hello, I have two identical sheets to collect application data, each for a different project type. We'll call them Sheet 1 and Sheet 2. They both reference the same budget request sheet. I have a "Budget Submitted" column on each sheet, which I want set up as a checkbox. I want the box to be checked if a project name on a…
-
Check for text value in multi-value checkbox cell
Hello all, I have a smartsheet where one cell (Item type) is a multiple check-box type, with the following values: 00 Project, 01 Submittal Task, 02 Vendor Drawing, 03 Product Data, 04 Samples, 05 Production release. The smartsheet is a tracker for our submittals for our company. The engineer will enter a line item (i.e.…
-
Seemingly Easy Formula For Countifs on a Rollup Sheet With OR...
I'm working on a rollup sheet where I want to count two instances in the same column. I am currently counting one instance and the formula works fine. When I try to add the second instance it returns unparseable. See below: Current Working Formula: =COUNTIFS({FY21 PEP Dec 20 Cycle (v1) Range 5}, "1", {FY21 PEP Dec 20 Cycle…
-
Commission Formula
Evening, Hope you're all well. I’m hitting a brick wall inserting a formula into a tracker for my sales teams commission structure which is being redone. It’s a tiered system whereby the salesperson will get commission in brackets until they reach target, and then a higher percentage on anything over target; (0.1% for the…
-
Subtracting values from cells not working correctly.
I have some countifs functions in some cells and I'm trying to subtract the value of one cell from the other. For some reason the value I'm getting when subtracting the cells is completely wrong and I'm not sure why. Unless I'm very mistaken I think 8 -3 -4 shouldn't be equal to 5.
-
Newbie: INDEX and cross-sheet reference.
Hi...and thank you for any help you can offer. It is quite a transition from Excel and Google Sheets to SmartSheet, but I know it's going to be worth it! I am trying to make a master sheet and then use different columns of data in other sheets. I know this works for 1 row: =INDEX({Symbol}, 1) I cannot figure out how to…
-
Need to ignore text (NA) in cell referenced in a formula
I have the following formula to determine Status: =IF(OR(TODAY() > [Due Date1]@row, TODAY() > [ Due Date2]@row, TODAY() > [Due Date3]@row, TODAY() > [Due Date4]@row), "Red", "Green") It works as expected when dates are entered into the referenced Due Date cells. However, if text (NA) is entered, I get a #INVALID OPERATION…
