-
How can I compare two cells in a cross sheet reference?
Hello, I have a COUNTIFS formula (below) that returns the number of items that pass all the tests. Basically, count an item if it's unique id is not blank, it's % complete is 100, and the risk area is Antiboycott. This formula works beautifully. =COUNTIFS({ExportUniqueID}, NOT(ISBLANK(@cell)), {ExpComp}, @cell = 1,…
-
Different types of zeros?
The formula below is creating multiple "zeros". See pictures. What am I missing? =IF(Balance@row = "ERROR", "ERROR", IF(Balance@row = "TBD", "TBD", IF(Balance@row = "DEDUCT", "DEDUCT", ((SUM(Jan@row:Dec@row) + SUM(CHILDREN(Jan@row:Dec@row))) - (((SUM(CHILDREN([Contract Amount]@row), "Change") + ([Contract Amount]@row)) -…
-
IF(OR with RYGG
I would like to automate a RYGG column, assuming that my team is entering there own start and finish dates but due dates will be created by senior staff. Yellow=In progress Red=Overdue Grey = Not started Green=Completed So IF start date is before due date, Yellow or if Finish date is before due date Green, etc. An…
-
Getting #INVALID DATA TYPE using IF(AND and IF(OR together
=IF(AND([Logo Attached]3 = 1, [Lobby Sign]3 = 1, [Column Sign]3 = 1, Mailbox3 = 1, "Green"), IF(OR([Logo Attached]3 = 1, [Lobby Sign]3 = 1, [Column Sign]3 = 1, Mailbox3 = 1), "Yellow", "Red")) Formula above. Would like to display green ball when all checked, Yellow when some checked, Red if none checked. Can't figure out…
-
Aggregate Summary Field Data from Multiple Sheets
I am attempting to consolidate multiple business worksheets, for an aggregate pie chart health status on a dashboard for executive management. See word doc for details Any suggestions to the best approach appreciated.
-
Counting overdue tasks from a lot of project sheets
Each project sheet has basically an identical structure and each line item is assigned to a specific person/user (Contact list). There are approximately 300 lines per project sheet. Each user has a report that shows only the tasks assigned to them from all the source sheets. Previously, we had a dashboard that showed each…
-
Add new row, calculated field not updating
I have a sheet that uses Hierarchy (on month) and is displayed in descending sort order. I have one calculated field that should be automatically calculated when a new row is added. When the person who will be entering data adds a new row, they are using "Insert Above" to add the row. The calculated field is not updated.…
-
Where is the COVID-19 dashboard
Hi all, In the press I read that Smartsheet has released a COVID-19 dashboard for the community - https://www.geekwire.com/2020/smartsheet-q4-revenue-51-78-5m-heres-company-responding-covid-19/ Where can I find this? Thanks, Victor
-
VLOOKUP plus a Dropdown selection???
I don't think this is possible, but thought I'd ask here. I have a situation where I would like to look up a value in another worksheet if the the value in a column adjacent to the lookup contains "DMP". I know how to write that formula, but if the value does not contain DMP, then I don't want the lookup at all, but want…
-
If 'Submit Date' is before the 20th of current month, Set 'Payment Due' to last day of month
Hello! Creating a sheet to help with Accounts Payable process to external providers. External providers will submit their invoice and payment details into a form that feeds into this sheet. Would like the formula to determine when that invoice is set to be paid, depending on the submission date. The formula in words.. If…