-
How to track yearly metrics wo updating my formulas each year?
Greetings, I'm curious is there a way I'm not aware of to easily track yearly metrics without having to go into each formula and update the year? Normally, I would create a formula that says count if one thing is met as well as if it's in "2024". However, I have to update my formulas at the start of each new year. Is there…
-
VLOOKUP Not Working With Primary Column Indented Values
I have a nested IF statement with a VLOOKUP that seems to have stopped working. For debugging, I've stripped out all but the VLOOKUP function and made the search value static, "DSHS". Formula: =VLOOKUP("DSHS", [Agency & Administration]23:[Agency-Div Sort Order]24, 22, false) "Agency & Administration" is the Primary Column…
-
Issues with "Record a Date" Automation
Hi all, Any idea why this automation isn't recording a date? We have a formula that's designed to return a value of "Reorder" whenever the inventory for that products dips below a certain threshold. The formula is working, but it's not being recognized as a trigger with the attached automation. Any advice would be much…
-
Display Most Recent Created Date AND Time
I created a simple check-in form whereby the system Created Date field is to be the entry time. It will be a running sheet where all punches are stored so, for reporting purposes, need to pull out only their most recent date/time for display - the 'time' piece being my challenge. (Also have "Check-in / Arrival" criteria…
-
Cross Reference Sheet with Drop Down so I can use Conditional formatting
HI there, So I have a reference sheet, listing down all Australian Public Holidays by State (multiple drow-down). What I would like to do is to identify these holidays in my Destination sheet, and display if the holiday is either QLD, VIC or NSW or all three. I am currently using a helper column to identify if the dates…
-
COUNTIFS Formula with CONTAINS in a specific date range only.
Hi everyone. I have a sheet with multiple columns. One column indicates the status of a survey and another column indicates the date the survey was distributed. I want to have a formula that will count the number of rows that contains "Survey Closed" in the status column but only for rows that have a specific date (1/1/24…
-
Formula with multiple answers
Hello, Is there a formula for a row that can have multiple answers? I have one column A with names, and depending on that name in column A, I want a different number to appear in column B For example If column A=Mary, return 245 or column A=James, return 548…..etc. I have 5 names with 5 different values associated with…
-
What formula can I use in the Main Sheet to bring in a Status based on the Reference sheet?
Good morning all, I have tried Index, Index-match, IF, IF Error formula's and I can't seem to get this cross reference to work but none do. Here is the breakdown: In the main sheet I want a column to auto populate 3 statuses: Not Started: when the reference sheet task columns have not been checked off In Progress: when the…
-
Formula works in one sheet but not others (cross-refs are updated)
Hi there, I have a formula in one sheet, which looks at the task and calculates the dates for each task, excluding holidays and weekends. =IFERROR(WORKDAY([Task Start Date]@row, VLOOKUP([Product Version]@row + Task@row, {ConcatProdTaskLookupDerek}, 2, false), {TechTarget Holidays Range 2}), "") I then use this same formula…
-
sumif date range, category, and total amount
Hi! I need to total up a dollar amount spent with a date range (monthly), and that matches a category name. Can anyone help with a starting point formula for me to work with? =SUMIF({Smartsheet Range 1}, "Digital Advertising", {Smartsheet Range 2}, {Smartsheet Range 3}, >=DATE(2024, 7, 31), {Smartsheet Range 3},…
