-
The correct VLOOPUP formula?
Hello I am needing a formula that will pull an employee level and assign it to the alias listed. I have used differed variations of VLOOKUP and nothing seems to work. Breakdown: Sheet #1 will contain a list of an employee alias's, sometimes the alias will be listed multiple times. In the column "Level (Formula)" on sheet…
-
30 Weeks Out - Formula Question
Hello, I have a project that has certain tasks that need to start a specific number of weeks ahead of the project end date. For example, I have 30 weeks out tasks, 29 weeks, out 20 weeks out, etc. On my project sheet, the END DATE is December 1, 2021. I need a formula that would tell me what the START DATE is for exactly…
-
VLOOKUP using flag or checkbox
How do I use VLOOKUP with Flag as the search value? I tried the following: =VLOOKUP("Checked", {Range 2}, 3, 0) =VLOOKUP("checked", {Range 2}, 3, 0) =VLOOKUP("true", {Range 2}, 3, 0) =VLOOKUP(true, {Range 2}, 3, 0) =VLOOKUP(1, {Range 2}, 3, 0) All of the returning #NO MATCH even if there is a row that has a Flag checked.…
-
Unable to edit column formulas
I'm the owner of numerous sheets and have been excited playing around with the recent addition of column formulas. However it seems like once I have created one I am unable to edit it, even as the owner of the sheet. It just gives me a little grey message saying "I have to be owner or admin of the sheet to edit". As a work…
-
VLOOKUP or INDEX
Dear all, I'm trying to use smartsheet as a Resource Management tool, unfortunatelly we haven't get 10,000ft yet. so what I'm doing is to assign the resources that we need in each project: Project Manager and Allocation % Business Analyst and Allocation % Tech Leader and Allocation % A person can be in different roles…
-
Index Collect + Max Date
Hi there, I'm having another head banging moment. I have a sheet where we track all project comments. There are 3 different types of comments (Program, Project and financial) I am trying to return a comment, where Comment Type = any of the above, and show me the Most Recent Comment of that type. I came up with the formula…
-
Performance issues
Hi All, I have 4 sheets with 5000+ rows & with 4 column formulas. I would need to add a 5th formula, which looks like as stated below. When I added it the sheet started to slow down, it was freezing, since the formula runs on the cell basis. Is there an option to optimize the sheets' performance, when cell formulas are…
-
Using the OR function within a count formula referencing another sheet
Hey all, Trying to work out how to add in the OR function to a count formual referencing another sheet. Presently I've got..... =COUNT(COLLECT({Activity}, {Row Type}, "Parent", {Status}, "Delayed")) But the Status could be any of "Delayed", "In Progress" or "Client Delay" Any help much appreciated!! Thanks, Anna
-
How to use a COUNTIFS formula that excludes multiple criterion in Multi-Select Dropdown column?
I'm trying to count the total number of values in a Multi-Select Dropdown column when criteria in another Multi-Select Dropdown column is not present. For example, let's say I have a FRUIT column with Apple, Orange, Banana, and Kiwi values. I also have a STATUS column with Rotten, Fresh, Stolen, and Eaten values. In rows…
-
Return a cell when one column matches another within an IF statement.
Morning, I've been struggling with how to return a specific value from two columns. We have created a risk register, and we want to show the overall risk rating for the project. The Overall risk is simply the risk of the highest ranked risk regardless of count. Initially, we are just using an IF statement, with a HAS on…