-
Check box if matching value in other sheet
Hi All, I am attempting to write a formula that would check a box if a value is present in another sheet, and I can't figure out exactly how to write it. I am thinking it would be a combination of IF, INDEX, and MATCH. I have two sheets, both of which have a column called "Protocol ID." I want to check a box if the…
-
Split Text to Columns Using Formula?
If I have a column with text like this: Mac:Air Mac:Pro Lenovo:Yoga Using formulas, how can I pull the data that comes before and after the semicolons? I want to end up with 1 column like this: Mac Mac Lenovo And another column like this: Air Pro Yoga Thank you.
-
Sumifs?
Hi, From another smartsheet I am trying to pull the total % of time spent by individual project managers on various projects that are in progress. I thought it would be a sumifs formula but it's giving me an answer of "0" instead of the percentage. Below, I have it first pulling the % column, then the Project Manager…
-
Nested If formula for RYG
Good morning, I'm having an issue with the following formula. The red and green come out, but the yellow comes back blank. Any assistance would be appreciated. =IF([% Complete]@row < 0.7, "Red", IF([% Complete]@row <= 0.07 - 1, "Yellow", IF([% Complete]@row = 1, "Green")))
-
Any way to have a formula automatically change input when a set of new rows added?
Hi, trying to automate a process whereby the change from week to week on a project is reflected in a graphic on a dashboard. Right now, I have the source sheet for the data copy the rows to a separate sheet once a week. I want a formula to simply find the difference between last update and the new one. This is what will be…
-
How to separate multi select values and collect distinct values?
Each period (P1,P2,P3 etc) we collect KPIs for our projects. In some new KPIs we have been asked to collect the answers involve multi select drop downs. What we would like to be able to do is to each period have a list of only the unique values. I have tried using a formula which uses JOIN, DISTINCT and COLLECT and it…
-
VLookup crossreference "#INCORRECT ARGUMENT SET"
Hi, I have been having trouble with VLOOKUP function in Smartsheet's. I have a sheet that I want to reference the Store# to pull over data such as the address from the other sheet. I have tried a number of things but the formula I am trying to use currently is: =VLOOKUP([Store#]@row, {ALL Store Openings (Master) Range 3},…
-
Counting Multi-Select Column value, based on another Column value
Given the following Grid - I am trying to count all instances of 'Apple' in a Multi-Select Column ('Fruit'), but only when another column ('Market') has a value of 'Market A' So in the above example, the answer would be 1, as whilst 'Market A' appears twice in the 'Market' column, 'Apple' only appears in the first instance…
-
Counting # of rows under a parent row that meets a certain criteria
Hi! I am trying to figure out what formula to use in the below situation: My sheet has parent rows that determine various phases of a project. Each child row has a status (in progress, complete, etc.) for each step (step 1, step 2, etc.). I am trying to figure out what formula would count how many "in progress" child rows…
-
IF Function Resulting in Specific Symbol Given Specific Inputs from other Columns
Hi all, Wondering if I could get some help on writing out a function. I know that it will probably be some sort of advanced and lengthy IF function but am confused on how to write it out. I have a Smartsheet that is used to track data inputs from a form. This data is related to a specific order coming in. I would like to…
