-
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?…
-
OR(HAS Function - limits to criteria?
I just learned from a ProDesk session that we needed to use HAS rather than CONTAINS, since will have more than one entry in a cell. However, we have one set of counts for three criteria in a cell. When I added the third one to the formula, it returned Unparsable. Is there a cap on how many HAS criteria you can count?…
