-
Cross Sheet Formula to list children in separate cells
Im creating an agenda sheet template where people can plug in a parent SKU, and then if there is children it auto populates all the children SKU underneath it, each child in its own cell/row. I can get it working manually adjusting formulas, but was hoping to get a more user friendly version that people can drag the…
-
Formula Index/Match -has any of
Hi is there a way to create a formula that will match to 'has any of' instead of an exact Match? Here is my current formula: =INDEX({Look Up Table Range 1}, MATCH([Sold To Name]@row, {Look Up Table Range 2}, 0)) If my look up table has the name "TEST 12345" I would like my formula to still pick up "TEST" and populate -so…
-
Info in email alert not same as on smartsheet
Good morning, I created a Smartsheet for employee anniversaries. The formulas and automations are all working perfectly, there's just one strange thing that is happening. In my Smartsheet, I have a formula to calculate the anniversary number which works great, however, the anniversary number doesn't always translate to the…
-
Need help with formula to alert when Actual Progress is behind Projected Progress
Hello I have been trying to nail this down for the past couple of weeks but falling short. I need a column that shows Red, Green, or Yellow based on Actual % Complete vs Projected % Complete. I am calling it Progress Alert. I need it to show the following: If Actual % Complete is 25%+ behind Projected % Complete show Red…
-
If function
Hello, I'm happing trouble getting an if function correct as it keeps returning #unparseable. I have a nested if function in a contact type column. Depending on the value in the Service column of a row, it would return a specific email address. I have pasted the formula below although I've changed the email addresses for…
-
Database Duplicate entries (Date & Item Based)
Hi All, I am struggling with a Item & Date based duplication formula. 1) For ease of reference I have filtered the database on Item "WL04". Various "ITEMS" with unique id's gets submitted into this database. 2) There can only be one entry per "ITEM" per "DATE" 3) As can be seen on the screenshot there are two entries for…
-
Adding extra stock to a field in a master sheet
Hi fairly new to working across sheets but have been using SS for a while. I have a master sheet with stock in it and I want to update with an additional sheet which has deliveries in it. I have carried out a Vlookup on the master and copied it to the delivery sheet and then added the stock item quantity to the delivery…
-
Index and Match Formula
Hello everyone! Just having some observation in the index and match formula that I used (data captured is from another file). Seems like the value of the formula only updates when I open the said file, then I'd need to save the file as soon as the values of the formula are updated. I noticed it because this column with the…
-
Is there an opposite way of writing an IF(ISDATE...) formula?
I have the following formula used in a sheet to change RYG symbol colors based on a targeted date status. =IF([Due Date]@row < TODAY(), "Red", IF(AND([Due Date]@row >= TODAY(), [Due Date]@row < TODAY(+7)), "Yellow", "Green")) This works, however - I need it to stay BLANK if the status is a certain setting OR if there is a…
-
Return a Minimum Date with A Condition
Hi there I am trying to adjust summary formulas so that they only return information from rows considered In Scope (so that data does not have to be removed from Not In Scope rows). Can anyone help with a formula that would return the MIN date from this group, only for the rows In Scope, ie Out of Scope is checked / true.…