-
Formula to calculate fiscal quarter when quarter starts on February
Hello, I'm very new to SmartSheet. I found a discussion from 2020 about doing a calculation for fiscal quarter and including year, but I cannot quite get it right. Our FY starts in February and ends in January of the next year. I'm trying to show the quarter and year as Q1FY24. Here's is the formula based on one created by…
-
Joining two columns together for rows that match criteria
Hello, I have a sheet with many columns from which I want to pull [Data]2 and [Data]20, in this format: "Data2 (Data20), Data2 (Data20),..." from another sheet. So far we have =IF(ISBLANK([Source1]@row), "", JOIN(COLLECT({Data2}, {Data5}, CONTAINS([Source1]@row, @cell), {Major/Minor}, "Major"), ", ")) And get Data2, Data2,…
-
Trouble with count and collect function when multiple values are within a cell
Hello, I am trying to count the times that a department is responsible for a task in the month of January from other sheets. I'm having trouble counting when there are multiple values within a cell. It will only count when the "label" is on its own so I'm thinking I need to add in a contains or has function but am having…
-
I am using the VLOOKUP function, but getting #NOMATCH?
I am using the VLOOKUP function to reference another sheet and pull in data. I have it in another area of my sheet pulling different data from a different sheet and it works fine, but this particular one keeps returning #NOMATCH. The screenshots below show the formula and field to lookup, along with the reference sheet and…
-
How do I take information from two sheets and auto populate into a third sheet?
I am trying to set up this smart sheet where it has a entity that someone can select from a drop down menu. Once they clicked any entity I had it V lookup from another sheet and it would auto generate the address for that entity from a different sheet into the cell next to it. I figured out how to do this part. I am…
-
Networkdays/Workdays Excluding Holidays and Exceptions
Hello, I am having multiple issues with my calculations for NETWORKDAYS and WORKDAYS working in Smartsheets. I entered default holidays into the Non-Working Days (holiday, exceptions) box in the Account Administration field assuming that once I set up the holiday/exception dates Smartsheet would deduct these dates from the…
-
Metric Sheet Automation
Hello there, Thanks in advance for the help. I have a lot of data sheets that I want to summarize in one metric sheet. The data will be grouped based on the "assigned to" column. Right now, if I add a new line to one of these sheets, I have to go into the metric sheet to manually add in a row under that appropriate…
-
Formula Issue
I am trying to write a formula to return the following: If Column A is not this, and Column B is this, return the result in Column B in Column C. Does anyone have advice? I think it should be fairly simply but I keep getting the unparsable message. Thank you in advance!
-
Formula errors: "flashing" #BLOCK errors and formula referencing wrong rows
Hi All, I have some sheets that have some heavy formulas and there are two issues: Whenever I update a cell (without formula) the entire sheet just start "flashing" with #BLOCK errors in pretty much all cells with formula and then these errors disappear after 5-10 seconds or so, but that's with every single update. I think…
-
Can SmartSheet Count & Return all other values besides "X"
Hello Everyone, I have a metrics sheet that is counting specific text ("Alpha") from a source sheet with the following formula: =COUNTIFS({Supplier}, Description@row, {Status}, "On Order") **This works** Ideally, I would like to count all the non-blank cells that do not have "Alpha" inputted. Can this be accomplished?…