-
How to automatically put formulas in the new rows that will come in from a form
Hi, so we have three sheets in the company, and one as a master sheet where it gathers all information from sheet 1,2,3. Everytime I will be inputting a new information on a form, the formula doesn't apply on that new row. is there a way I can automate this that if there is a new row, it will have the formula of the one…
-
Use IF/AND or INDEX/MATCH?
I have a sheet (top) where Site Code and Sub-GL are drop downs. There are hundreds of corresponding site/sub-GLs listed in my reference sheet (bottom). I am looking for the correct formula to return value of "2021 OP2 Total" when Site Code and Sub-GLs are selected match a line in the reference sheet. I can't seem to make a…
-
Calculate Variance in Days
I need to create a formula that calculates the total number of days an employee is in training. One cell in my sheet is the Training Start Date. One cell in my sheet is Today's Date I need this cell to calculate the number of days between these to days. I tried =NETDAYS(start_date, end_date) and it give me #UNPARSEABLE
-
How to calculate a COUNTIF when using a date 01/01/2020 month to date
Good Morning Everyone, I am trying to create a formula that pulls from a master spreadsheet that calculates the number returned based on a date from the data sheet. I have columns that indicate month to date, September, August, July, June, May, April, March.... =COUNTIFS({LPSC tickets Range 1}, "Production", {LPSC tickets…
-
Sumifs with Multi Select DropDowns
Hi all, this is very simple, but I am pulling my hair out! I am trying to do a SumIfs, where one of the criteria is a multi select dropdown. My first attempt is =sumifs({column to be totaled}, {Multi select Column}, Option 1) If I do this, then it only Suns when Option 1 is the only option, not where option 1 and and any…
-
Count IFS
I am trying to "countif" the Last Name and the First name in Summary. I figured out how to count the last name =COUNTIF([Last Name]:[Last Name], "Roman") but what do I have to do to capture the first name if it is in a different cell? =COUNTIFS([Last Name]:[First Name], "Roman" "Kate"). I can't figure out how to capture…
-
Date updating to Next Day using Index, Match, Max, Collect Formula
Hi, I'm working with two Smartsheets. (1) One sheet collects the data from a Smartsheet form. I added two columns to this sheet using the Auto-Number/System Feature (under Edit Column Properties). This features stamps new row entries with a date/time (Created) and by whom (Created By). I'm curious about the cell surrounded…
-
Contains "Kansas" is returning TRUE when actually contains ARKANSAS
My State field is multi-select and users can select: Kansas or Arkansas or Kansas and Arkansas or other. I want to return a text value "Is Kansas" in a separate column if the State column contains Kansas or contains Kansas and Arkansas but will not return "Is Kansas" if the State column has only Arkansas (or other). When I…
-
Populating a field when two checkboxes are checked
Hi all, I'm just starting to learn more about formulas and am a little stumped on this one. I have two columns that are checkboxes: "Dates Received" and "Prices Received". When both checkboxes are checked, I would like it to populate a field in another column with "1". When neither are checked, or only one is checked, I…
-
Problem with vlookup calculations
I am having a new problem with an existing Smartsheet that has worked fine until now. Existing workflows have stopped working. The problem does not appear to be workflow, but something else going on. We have a workflow that triggers off of a calculated field that is based on data in another Smartsheet. Users enter data…
