-
Getting characters from before and after a text
Hi, I have this value in a cell. I need some help to create a formula that can find the last '$' sign and get maximum of 4 number after the '$' sign and 6 numbers before the '$' sign from the data below. "FB12345 $12345 ABCD 12345 SC FB12346 $1234 ABCD 12345 SC FB12347 $123 ABCD 12345 SC FB12348 $12 ABCD 12345 SC" The…
-
Return Multiple Values with Index/Match or VLOOKUP
Hi all, I'm working to develop a system where Smartsheets will collect all matching names (First Last in the same cell) and list them together based on what company they work for (listed in an adjacent cell). INDEX/MATCH shows promise, I've used it before as an array formula in Excel however in Smartsheets I cannot figure…
-
If Column 1 contains "Yes", Vlookup data from Column 2.
Hi Community, I want to lookup data, based on data on my current sheet, If Column 1 contains "Yes", Vlookup data from Column 2. IF Column 1 CONTAINS "YES" VLOOKUP Other Sheet Reference,2,false Any way to do that? Thank you in advance! 😀
-
Formula to change status symbol if a task is not completed before due date
Thanks guys, new to Smartsheet and need some help... Having issues with changing the status symbol to Red, Yellow, Green, or Gray based on project completion percentage column (%Complete) and start/due date columns ("Start Date" / "End Date"). I would like the following: Gray - If %Complete equals to 0 and start date has…
-
If(OR(AND statement
little help - been working on this too long If the %complete is100%, ie. =1 then the ball should be gray no matter what If the job is not 100% and finish date has past, then red, If the job is not 100% and finish date is within 7 days, then yellow, otherwise green =IF(OR(AND([%Complete]@row = 1, "Gray"),…
-
Cross sheet Formula
What formula should I use to populate column E in "GHG Electricity" adding monthly total (Columns E to P) from cross sheet "SPI Data" for each site (in Column A) with Electricity (in Column C) ? THANKS!!
-
How to write formula to add few columns in a row from selected rows
How to write formula in another sheet referencing this sheet to add few columns in a row for selected rows ? for example, sum all electricity for all sites e.g. Aizu, Belgium, Bucheon...etc for Q1 (Jan, Feb and Mar). THANKS!
-
Can i exclude first 25 and last 25 rows from a Column, and also seperate the middle rows to a sheet
I have a Line count of 100 rows in specific sheet, i want only 50 rows from 26 to 75, which intern excludes first 25 and last 25 rows, and also can i seperate the file with the middle 50 rows
-
IF CONTAINS VLOOKUP
Hi, I'm trying to do the below formula, that if Column 1 Contains "No Match", VLOOKUP Column 2, and get Value from referenced sheet, is that possible? see below. =IF(CONTAINS("No Match", [Column 1]@row:[Column 1]@row), " ", " ",(VLOOKUP([Column 2]@row, {Sheet Name Range 1}, 2 ,false ) Thank you
-
Nesting IF statements that use the FIND function
Hello all! To preface, i've searched into nesting IF statements on the community and I believe i'm following the correct rules, but my result is #incorrect argument every time. I made this function: =IF(find("This Long Name",[Full Name]@row)=1, "TLN"+MID([Full Name]@row, 17,20), [Course Short Name]@row) The column Full…