-
VLOOKUP returning #INVALID REF
I am receiving the #INVALID REF error in smartsheet and I am not sure how to correct it. =VLOOKUP([PART NUMBER]1, [PN]1:[PART DESCRIPTION]1800, {2,3,4,5}, false) Below is the excel sheet formula that does work but does not flow over to the smartsheet. =VLOOKUP($Q6,$A4:$D25014,{2,3,4,5},FALSE) If you look at the excel…
-
Combining OR Function with INDEX MATCH.
Hi, I tried using Combination of OR Function with INDEX MATCH and phrased it as below =INDEX({Ref Column}, MATCH(OR([Column #]@row, {Column # SheetA}, {Column # SheetB},{ Column # SheetC}), 0)) but it if throwing #INVALID DATA TYPE. whereas =INDEX({Ref Column}, MATCH([[Column #]@row, { Column # SheetA}, 0)) is working…
-
Formula - Adding together certain assigned point values
Hello, I have created the below formula that works up until the bolded part. I am trying to add this to the below formula: If Less than 100 it gets zero points. If between 100 & 499.9 it gets 1 point If between 500 & 999.9 it gets 2 points If greater than 999.9 (or 1,000 or more) it gets 3 points. I am not sure how to add…
-
IF > TODAY formula not working
I've been trying for hours to get this formula right and I think I've tried every combination. I have a date field. It can either Have a date Be Blank Have TBD Have N/A If it has a date AND the date greater than today OR if blank OR if it's TBD = not check If N/A OR date is passed OR it's blank = checked This is what I…
-
Parent Health Status of Children Rows Based On If Certain Criteria Is Met
Hello, I have a Gender column with these options: Female, Male, Transgender, Non-binary/non-conforming, Prefer not to respond I need the parent row to reflect a status of RED ball if there is no 'Female' represented in any of the children. I currently have this formula, but feel I'm missing significant COUNTIF references:…
-
Pulling in Established Data from 1 Sheet into Another Sheet as a Drop Down
Hi All! I have one base sheet of HR job posting with several descriptive column headers, and one of the columns is a Job ID number. Job ID numbers are hand-keyed and unique. I have a second sheet of candidate names and I want to be able to select an active job ID to assign to that candidate from the original base sheet of…
-
Copy a cell from another sheet with criteria from another column that has multiple same entries
I have two sheet, Test of Project Intake, which has many columns of data. I want to copy the Project Name column when the Lookup Combo has "Pending-PPRB". The problem i am having is the Lookup Combo column has many entries of "Pending-PPRB". For each of those entries I want to copy the Project Name to the New Projects…
-
100% in conditional formatting
Hi, how can I express 'greater than 100%' in conditional formatting? This is my column: And this is the conditional formatting command I'm using: I've tried 0.01, 1, 1.0 and 100, and it just returns everything red. Any help would be very much appreciated.
-
3 row automation
I am new to SmartSheet, so I'm not sure what it's capable of. I want to have a template of 3 rows [2 indented behind the first] that I can link to a form for adding rooms/lots to an existing job. Or, instead of creating a template, automate the creation of indented rows upon row creation from a form. I guess this might…
-
Is there a formula in smartsheet that would create a 2 way link between cells?
Hello, I have multiple sheets that I would like to link all into one Master Sheet. I want the cells to have a two linking methods between each other. If a cell changes in the Master Sheet, I want it to reflect to the other sheets and if the other sheet decides to change the cells, it would reflect back to the Master Sheet.…
