-
INDEX MATCH is returning #NO MATCH or the wrong email address
I have a Smartsheet that I have populated with INDEX MATCH, and it works great. My last column is to pull in the email addresses of all the employees, based on their employee ID that has previously loaded into the Smartsheet via an INDEX MATCH. So my [Site Contact 1 ID\@row is data sourced from an INDEX MATCH already. I am…
-
Formula to look at specific column/cell depending on the current month
Hello, I want to create a dashboard widget that will show employee FTEs for the current month. I currently have a sheet (Capacity Plan) that list out the employee's name, and then has the assigned projects with FTE breakdown as child rows underneath. Then I have row that calculates the total for the month. For the widget,…
-
Formula to concatenate multiple "tab" characters doens't work as expected
I created a formula to try to create an indented structure for reports by concatenating multiple "tab' characters based on the indent level. However, the formula doesn't work as expected. Instead of ending up with multiple "tab" characters, the output is either an empty string (when lvl=0 or lvl=1) or it's the number zero…
-
What wrong with this IF OR Loop statement
=IF(OR([Site]@row= "Kelowna", [Site]@row="Vancouver", [Site]@row="Victoria"), "British Columbia", IF(OR([Site]@row= "Halifax", [Site]@row="Moncton", [Site]@row="St Johns", [Site]@row="Grand Montreal"), "Eastern", IF(OR[Site]@row= "Central", [Site]@row="Tannis", [Site]@row="SW Ontario", [Site]@row="Toronto") , "Ontario",…
-
Looking for percentage, getting incorrect arguement
I am sure there is some simple answer, but I brain hurts. We are developing calculations and metrics to quickly assess the status of a project. I am trying to find the percentage of Critical Path taskes up to a Go Live date, that are marked at risk. My first attempt worked, but I received a 'Divide by Zero' error. Now I am…
-
Average Collect Issues
Hello! I'm trying to write a formula that will average the amount of time a type of request takes to complete by month. I'm referencing another sheet in this formula and just can't get it to work. Here's what I have so far: =AVG(COLLECT{Hours to Complete}, ISNUMBER(@cell), {Request Type}, "Quote Request", {Submitted Date},…
-
multiple VAT % sum
hello all I think this simple but I cant get it right for sum reason I have 4 columns VAT % , NET Price and VAT, plus Inv total basically VAT % has a drop down of different vat charged 6 options I want my formula to look at option picked in column 1 (VAT % ) and the amount in Column 2 Net price to tell me in column 3 ( VAT…
-
IF Formula with Multiple Criteria
Hello! I have a software enhancement sheet, and I am trying assign priority to each enhancement based on the level of effort (LOE) + change priority calculated score. The LOE can be one of the following: 1 - Unknown, 2 - High, 3 - Medium, 4 - Low, 5 - None The change score change range from 4-25 The breakdown for priority…
-
Overall Project Status based on frequency?
Hi, So I have the following sheet set up for projects where each Group may have more than one project in different statuses: The order of status are the following: 1) Started, 2) In Progress and 3) Completed. I am trying to set up a formula that will return the most frequently populated Status per unique Group and also…
-
Help with a formula to count how many projects with a brand have the status of "approved"
Hi there, I'm trying to figure out how I can count the number of projects for a brand have a specific status out of the total projects for that brand. Also, I'm referencing a different sheet for these formulas. I already have the formula to count how many projects are associated with a specific brand, but now I want to set…
