-
Updating a Date Column after another cell is updated.
I am trying to have a Date Column update with today's date when a single select option is chosen. I don't want to use the system update date column because that will get updated whenever any cell is selected. For example: I have a single selection column with several options: Estimating Complete, Quote Sent, and Sale. I…
-
Use Wildcards in an INDEX MATCH formula
Hi everyone, I need to use INDEX MATCH formula to get a DS# with a CNDT# in another report. The easy formula would be: =INDEX({DS Report Range 2}, MATCH([CNDT Number]1, {DS Report Range 3}, 0)), "") but the thing is that sometimes there are more than one CNDT#'s in a single cell. Since INDEX MATCH looks for the exact match…
-
RYGB Symbols Average Formula
I currently having issues with creating formula that gets the average of 4 cells that have the option of 4 different symbols (Red,Yellow,Green,Blue). The goal is to show the average of the four cell symbol's in one here is what I have so far. =IF(COUNTIF([PW Goal]3, [BB Goal]3, [JQ Goal]3, [MC Goal]3, "Red")) = 4, "Red",…
-
Survey Form(S)
I'm currently working on Survey Form. I have made one for one "X" department. I need to make another survey for for "Y" department. The two departments have different heading but the two have same questionnaire but different employee names. How do I clone the first form into the second. And is it possible to the results…
-
Match and Index functions to create a table referencing another sheet
Hello, I need help creating a formula which will work. Sheet 1 - contains my data Sheet 2 - Contains the tracking matrix I am trying to create I need a formula which will look at sheet 1, determine if an inspection has taken place within a certain month, and then return the date of that inspection into sheet 2. I've been…
-
Formula If & AND referencing form another sheet
Is there a formula for the following scenario referencing from another sheet? if "checkbox = ticked under Unique column" AND "Fruit = Apple" Then display [color]@row Refer to below, result I want to get is "Green" =IF(AND({SheetA Range 3}=1, {SheetA Range 2}=Apple), {SheetA Range 4}) I tried with above formula, but didnt…
-
Incorrect Argument Set: Nested IF Formula with 3 Logic Statements
I have the following formula that works and I would like to add one more criteria, if Bid? = "Bid" then return 100% in %Complete Column This is the original working formula: =IF([Include in Estimate]@row = 0, 1, IF(OR(NOT(ISBLANK(Units@row)), NOT(ISBLANK([Fixed Cost]@row))), 0.25, 0)) This is my edit which returns the…
-
How do I get a formula cell to return to blank when the corresponding cells don't have any values?
I am using the formula below to calculate the number of years between today's date and a persons start date in another column. =(TODAY() - [Start Date at TDG]1) / 365 If the start date has not been entered, the Years @ Company cell is showing "0". How can I get the cell to show as blank until data is entered in the start…
-
VLOOKUP Multiple Matches
I have a table that sometimes has duplicate entries in a column. I'm doing a VLOOKUP on this column, however, obviously, if there is more than one match, this generates an error. What I'd like to do is return a match for the most recent duplicate entry, or in the case of a sheet that is filled via a form, the item farther…
-
Can't perform date calculations on a Date returned by a cross sheet JOIN(COLLECT(VLOOKUP function
Hi, To populate DueDate3, I'm using: =JOIN(COLLECT({GUT Column 7}, {GUT Column 2}, <>"Postponed", {GUT Column 2}, <>"Rescheduled", {GUT Column 1}, VLOOKUP(TaskName1, {GUT Column 1}, 1, false))) to return a date from another sheet. (It is a date data type in the other sheet). In another cell, I want to use something like…
