-
Complex formula is calculating random cells and skipping others.
Hi everybody, So this will be a long read, but i hope somebody will help me solve this craziness, that already ate 2 days of my life. So i have this formula: =IFERROR(IF(VLOOKUP([Seller]1; {Range Seller | Bonus}; 2) = 1; IF(VLOOKUP([Sum]1; {Bonusai Range 1}; 2) < 1; [Sum]1 * VLOOKUP([Sum]1; {Bonusai Range 1}; 2);…
-
Can I importrange data from a Smartsheet into a Google Sheet?
Hi There, Is it possible to have an importrange in a googlesheet that is pulling live data from a Smartsheet? This is what I am trying to achieve: We need to implement a consultant request process. Requester fills out form with all necessary information, that is routed for approval from Ppl Ops, Finance, and Dept. Head,…
-
Return null if there is no date
Easy question: in the formula below, I need to add a final (or starting?) condition that if there is NO date in the Due Date column that no colored ball will appear. Right now, if a task is added and they haven't put in date it is showing Red and that is throwing the metrics off (as the metrics are looking for the Red…
-
How can I make other categories fill after a name from a dropdown has been selected.
I have a form for users to use that looks like this: I would like to make it so that when a user pulls their name from the the drop down menu "full name" their email address and phone number are automatically populated. I was thinking I could do this with vlookup from another smartsheet, but I am having problems getting it…
-
sheet summary, countifs task status
I have a simple formula, i can't seem to get working. reads - invalid data type In the sheet summary i want it to count the task status, (Red, Yellow, ...so forth) When i put in specific range ie [Task Status]2:[Task Status]100 - it seems to work fine. but when i wish to calculate the entire column. (*some rows may be…
-
Flagging Parent, if Child flagged and if parent % complete = 1
I have a project management sheet set up for % complete not using the sheet logic, but using formulas so I can have them fed by check boxes. I also have a manual 'star' flag so I can make notes (if something is done, but waiting for feedback) that turns yellow to remind me to look at it. I am looking to flag the parent…
-
Averageif Formula
Hello All, I need your help to define a formula, I have Country column and Networkdays columns so I need to avg networkdays for the specific country. could you please help me. I am using : =AVERAGEIF({Table 1 Range 12}, {Table 1 Range 1}, ="France") But I am getting : Invalid operation so basically I am not able to give…
-
Formula IF, nest AND in OR
Hello there, I have two columns of data with days employees work with drop down selection (M, Tu, W, Th, F, S, Su) and a cell linked date $TODAY$1. I want to return today's date if it is on the day of the week the employee is schedule to work. The formula below have no errors, but it is not returning the values on test…
-
Adding Percentages Together for Total
Hi! I'm trying to figure out a way to add the percentages from the Column labeled "Capacity" with a function and have them automatically tally together in the "Total Percentage" column. The percent value is being calculated with the Nested IF formula that I have shown. The point is that I will eventually add these columns…
-
Nested IF Formula - with drop down list
Hello, I'm trying to figure out how to create a really long IF formula based on options provided in a drop down list. I'm having students select their major from a drop down list. Depending on what major(s) they select, I'd like to auto populate the division that that major is connected to in a different column. For…