-
Index Match Formula
Hi everyone! I am trying to use the Index / Match formula to display data from different sheets into one. But I am having trouble getting it to match, I enter the formula as below but I keep getting no match. =INDEX({Johnson Range 1}, MATCH([Task Name]@row, {Johnson Range 2}, 0)) Any help would be much appreciated!
-
How to check a box if a date is in the past 7 days & next 14
Hello, I want to check a box if the following criteria are met. I want to then base an automation to send out rows that are not complete and were due in the last 7 days / due in the next 14. Criteria: Status is not Complete Finish is in the past 7 days (overdue) Finish is in the next 14 (look forward 2 weeks) This is the…
-
Creating a status using project stage
My data is a list of jobs all having 8 applicant stages. These 8 stages will ultimately represent 6 statuses. I am looking for a formula that will let me assign a status to each job based on the 8 applicant stages. Because jobs can have new applicants and an applicant in the offer step, I need it to work from the furthest…
-
look up value in other sheet when searching data isn't exact match
Hello I have a column in a project task sheet called Customer Name, where users enter the short version of a customer name: I want to populate line number in the project task sheet with data from a separate active projects sheet but the customer name in the Active Project sheet is not an exact match to the project task…
-
Cell Formula For Traffic Light Symbols
Hello, see attached screenshot. in the yellow cell (top right) I am wanting to have a SUM of the below 'Days To Complete' cells, but only those that have the Green traffic lights. As you can see this is a team members project list, so we are trying to estimate the work ahead for this team member. Can someone please help…
-
COUNTIF = BOOLEAN EXPECTED
Hi I want my smartsheet to count the number of checked boxes in a specific column. I have formulated: =COUNTIFS([Ceremony/Party]:[Ceremony/Party], 1). However, it is cominh up with "BOOLEAN EXPECTED"?
-
Countifs forumla with 3 criteria's
I'm have used a countifs formula to count the total number of jobs per month that have been launched in my business using the following formula: =COUNTIFS({Project Launch Input 2021+ Range 3}, >=DATE(2022, 6, 1), {Project Launch Input 2021+ Range 3}, <=DATE(2022, 6, 31)) I now what to be able to count the number of jobs…
-
VLOOKUP CROSS SHEET REFERENCE
MY QUESTION: I am using a vlookup/cross sheet reference to populate when specific criteria are met, such as vendor name and contact number. =IFERROR(IFERROR((VLOOKUP(Payee@row + [Agrmt No. 1]@row, {MATCHING ACTIVE CONTRACTS Range 6}, 6, false)), (VLOOKUP(Payee@row + [Agrmt No. 2]@row, {MATCHING ACTIVE CONTRACTS Range 6},…
-
Is my SUMIFS formatted improperly?
I'm looking to sum $ column when Status column is "Contracted" and when Job Type column is either "Balcony" OR "Roof Anchor" This is what I have: =SUMIFS([$]:[$], Status:Status, "Bidding", [Job Type]:[Job Type], OR(@cell = "Roof Anchor", @cell = "Balcony")) The result is: #INVALID OPERATION I feel I'm probably missing…
-
IF and CONTAINS formula issue
I want my formula to tell me if the “name” contained in a cell of the current sheet is present in a contact list column from another sheet (could be only once or several times). These formulas give me a “No”: =IF(CONTAINS(Name1,{123 Plan Assigned To}) "Yes", "No") =IF(CONTAINS(“Name X”,{123 Plan Assigned To}) "Yes", "No")…
