-
NEW! Your survival guide to formulas is here. Meet the Formula Handbook.
Hi Community, Whether you’re summing child rows, calculating budget minus actual, or doing cross-sheet data lookups with multiple criteria, formulas are a must for tailoring solutions to your needs. The new Formula Handbook is here to help formula beginners and pros alike. This template contains: A glossary of all…
-
Formula issue
Hi Braine Trust I need some help with a formula I have Mobilization Date and need to create reports that will show me a list of people who have mobilized - Any one Mobing Week Prior to the Mob Date Any one Mobing The Week of the Mob Date A 2 week Forecast ( so two weeks prior to the Mob Date A 3 week forecast (so three…
-
Countifs date is this month and a day of the month
Hello all, I am using the formula to count from a list of dates in another sheet to find all entries that are from this month. =COUNTIFS({Date}, IFERROR(MONTH(@cell ), 0) = MONTH(TODAY()), {Bay}, Bay@row) I would like it to also register if it is the 1st of the month (for the column named 1st) and the 2nd of the month (for…
-
Subtract Invoice amount from Total based on Status, in Parent-Child relationship
Hi all, I am new to create sheets, but I am trying to create a solution to track forecasted invoices that need to be sent out. I want to put each invoice total separately and when the status is marked as "Complete" I would like to have it subtracted from the Contract Amount to show the Contract Balance. I set it up so the…
-
Formula for calculation
Hi Guys, I'm stuck with one critical problem, where I need one formula. I have two sheets Sheet A (contains only applied leaves, not all the dates range) Column 1 - Leave Start Date, eg 01 jan 24 Column 2 - Leave End date, eg 03 jan 24 Column 3 - Employee code, eg ABC001 Sheet B (contains entire range of dates for…
-
Automate Weekly Copy/Paste of Hierarchy
I have a weekly tracking sheet that I update each week by doing the following: 1) Copy/Paste the current week's hierarchy (lines 546-561 in screenshot) to the last row of the sheet, 2) Update the week column, and 3) Clear some of the data for the new week. While this doesn't take a long time for one sheet, I do a similar…
-
IF Statement to Display Symbols
Formula variable question for an IF Statement. Ok I have 5 columns (see screenshot below). This formula will be added to the one on the far right (Certification Complete). Now I would like to assign a value to each to trigger the symbol that will display. Basically the way it works is that all 5 columns are required, if…
-
EDATE equivalent in Smartsheet - IFERROR(DATE(YEAR....
I found some examples in previous discussions and tried it =IFERROR(DATE(YEAR([Sale Month]@row), MONTH([Sale Month]@row) + 1, DAY([Sale Month]@row)), DATE(YEAR([Sale Month]@row) + 1, 1, DAY([Sale Month]@row))) my theory was if Dec 1 was +1, then Jan would be +2 etc….it seemed to work until Feb onwards it keeps returning…
-
Formula help needed - if/then
Is there a way to make my formula (below) that if the "type of CPG" says "priority" in it that it can be - 21 days and all other types would be - 14 days? hopefully that makes sense!
-
Report totals formula
Hi, I have an example below. On my report, months remaining for each store is correct, based on inventory/sales pace per month. However, in the total, I cannot add the months remaining to get an accurate number, which the report is trying to do. (It doesn't work if any of the sales pace numbers are below 1. I need to…
-
Help with IF formula returning "invalid operation" message
I am applying this formula and get an "invalid operation" return message. Essentially, I need a function that checks if the latest or most recent date in which a member was met by Sales team is greater than 1st of Jan 2024 . In such case, it should return "Yes" and if not it should return "No". Can somebody assist with the…
