-
Formula for Counting an Item Once Per Date
I am trying to come up with a formula to count the number of times a 'meeting type' occurs within a column. The issue is, I only want to count the 'meeting type' once per day whether it only occurs once or 20 times. So in the case of my screenshot, I would be returning the following: CIty Council - 3, SPIN - 2, P&Z - 2.…
-
Concatenate two cells
Hi, I need to concatenate data of two cell into one cell. I just use the formula "=[Cell1] + [Cell2])", which worked fine and gave the result "Cell1Cell2", however I need to have space or underscore between values of two cell like, "Cell1_Cell2" or "Cell1 Cell2". My practical requirement is: I need to concatenate "date"…
-
Linked Cells with Numeric Value not working in Chart Widget
Hi, I have a roll up of numbers for the total of checkboxes checked off in our main data sheet. I've linked that totaled value into a separate sheet via the link cell capability. That is somehow not getting charted, and is showing up as "Invalid data"? What am I doing wrong?
-
Calculate days past due based on end date
I want to populate a column labeled "Days Overdue". I want to look at my column "% Complete" if "% Complete" is not equal to 100%, then get TODAY() date - "End DATE" and populate the value into "Days Overdue" column, else 0. In excel I can accomplish this with the following IF statement. =IF(% Complete<>100%,TODAY()-END…
-
Invalid Data Type using Find function
Hi All, I can't seem to find the error in my formula. I'm trying to use FIND() to locate a unique id # SE2 within a column on another spreadsheet. The column in the other sheet is a text column. =FIND("SE2", {**2019 Event Builder Range 1}) I keep getting back Invalid Data Type. I'm not sure what I'm doing wrong. Can anyone…
-
VLOOKUP is data type of TEXT instead of DATE - sorting is off
I am currently creating an report which looks up dates based on specific milestone tasks over a number of project sheets. When the vlookup calculation is entered and I hit the Return key, is it giving me an alert that the value is not in date format. Being the admin, I am able to override this. But when sorting, it sorts…
-
Index and match multiple sheets
Hi, I have an index and match formula set up for 1 sheet and it works great but how do I handle it if I want the index to look at data from multiple sheets to pull the correct information. My current formula is: =INDEX({Site Number}, MATCH([Site ID]62, {Site Name}, 0)) I would like the index to look at the site number on…
-
Using One Checkbox to Check Another
We have a task sheet that uses 3 checkbox columns (Sent, Received or Done, N/A). We have a formula that counts the number of tasks and how many are completed based on the Received or Done column. We are trying to make it so if the N/A box is checked, it automatically checks of the coinciding Received or Done box. Please…
-
Automating RYG balls
I would like to automate the RYG balls in my sheet to turn “Green” if the Due Date is in the future, “Yellow” if the Due Date is today, and “Red” if the Due Date is in the past. Is this something Smartsheet can do?
-
Fighting an IF formula
I'm not sure what I am doing wrong. I am trying to get a response for "if the issue date is more than 10 days from the begin date AND the task is not 100%, show Late. Otherwise show 0. I will then be writing a report that shows only late. =IF(AND(NETWORKDAYS(Begin1, Issue1) > 10, "Late", "0", [%Complete]5 < 1), "Late",…