-
Vlookup match for exact AND approximate matches - single column copying workaround
Hello! I have a process that currently involves multiple sheets and requires manual copy & paste to complete. This is not a scalable solution for our business, so I’m trying to build a workaround to combine data from several sheets and automatically update data in some sheets when certain actions are triggered. Some of…
-
Does not contain this OR that
Trying to get the correct formula that will return the forecast amount for every column except those that have the words "closed" or "no response" in the Sales Stage Column. I can get it to work if there's just one qualifier: =SUMIFS([Forecast Amount]:[Forecast Amount], [Sales Stage]:[Sales Stage], NOT(CONTAINS("Closed",…
-
How do I create a specific formula?
Hi, please refer to the picture below. Is there a formula that can pre-populate the 'Hours' columns based on the selections made in the 'Type' and 'Size' columns? For example, if I select 'SOW' and 'Medium' in the 'Type' and 'Size' columns, then I want the formula to automatically populate '320' in all 3 columns (1. Est.…
-
counting from a multi select cell
I do not have clue where to start with this formula I working on a behavior observation and i want to count the number of behaviors observed. Problem is countif({column 1}, "PPE") won't work if it is not first in the cell on a multi selected drop down cell. any help is welcome I will also need to be able to count the…
-
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…
-
Why do I have a formula that is not working in sheet summary, yet is working in another sheet.
I have a column with some rows marked as "Active /Won" in the "Project Status" column. I want to sum all of those values from the corresponding column "Project $ Value". I have done this successfully in another sheet, but I cannot get this formula to work on my sheet summary... In my sheet summary I get "#invalid…
-
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.…
-
SUMIF Question
Hello, I am trying to sum a column in my smartsheet that has numbers, but only want to sum those with a status of "Business as Usual"....seems pretty straight forward, but I'm hitting walls and have even reached out to someone who has far more advanced skills than I do. Here's the current formula I am using: =SUMIF([# of…
-
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…
