-
HELP! Summary Report to pull from different sheets based on multiple drop down selection
Hello! I am new to Smartsheets and am trying to build a Summary Report based on drop down selections and data from various sheets. I have a TON of data and too many cells so I've had to spread it out on a sheet per month. I have two drop down selections. One is to select the month and the other is to select a division…
-
Only excluding the first weekend
Hi, I'm currently using a formula to spit out a certain number of days between two dates. I want to exclude the first weekend if it falls within the first 3 days, but otherwise include it. Currently to exclude weekends entirely I'm using =NETWORKDAYS([SALE DATE]21, [OUTPUT DATE]21) - 4 I'd like the formula to work like…
-
IF(OR(ISBLANK) Statement
I am trying to set a colored dot to Green if the project is on time, Red if it is late and Yellow if it is not started ([Date Completed] = blank). The Red and Green work, but I cannot get the yellow dot. Any help would be greatly appreciated. This is the formula as it stands. =IF(OR(ISBLANK([Date Completed]1, "Yellow"),…
-
Finding Value in a Cell In Another Sheet
In Sheet 1, I want to COUNT instances of value across a range of cells in Sheet 2, with some cells containing multiple values, separated by commas. Let's say Sheet 2, has the following values in a range of rows: SHEET 2 1: | blue | Group 1 2: | blue, red | Group 2 3: | green | Group 1 4: | red, yellow | Group 1 5: | red |…
-
COLLECT in CHILDREN based on another column's value in CHILDREN scope
I am trying to find the min value of CHILDREN in a column of numbers where the value of the [% Complete] cell in the row of that column is not = 100%, but, again, only for the CHILDREN. Using =MIN(COLLECT(CHILDREN(), [% Complete]:[% Complete], @cell < 1)) or =MIN(COLLECT(CHILDREN([Days Till Due]:[Days Till Due]),…
-
Sumif
Hi, I am trying to get the sumif function to work for me and I am having trouble. I am trying to count the dollar amount of invoices in column Invoice Amount if the the JOB# column has "BO" in it. These are the formulas I have tried so far: =SUMIF([JOB#]:[JOB#], IF(FIND("BO", [JOB#]:[JOB#]), [Invoice Amount]:[Invoice…
-
How do I Subtract a series of days from date column?
I need to calculate a series of dates for a newsletter production cycle. I want to take the Expected Release Date ie: 25 Aug 2019 and in seven column cells, beginning with ten days from the Expected Release date, then show 8, 7, 5, 4, 3, 2 days calculated dates from the Expected Release date. I can perform this easily in…
-
Referencing column in another sheet from row 2 only
I am trying to set up a formula that counts the number of cells containing information in the first column on another sheet. I just don't want it to count the text that is in row 1 (ie I just want it to count from row 2 onwards as I am constantly adding data to column). I don't want the formula to even reference row 1 as…
-
Forecast using if function
I am working on a budget tracking sheet where I have a forecast column. The forecast column is dependent on if there is a value in an estimated cost column or actual cost column. If there is no value in the actual cost column, then the forecast column is equal to the value in the estimated cost column. If value exists in…
-
Family Counting
Im looking for a formula that will allow me to index down through a number of children and count the number of decendents under that child. Any help?
