-
easier references to other sheets
Hi all, I prefer to use references of the form {sheet_name Range 1} rather than using the "link from cell in other sheet..." because the formula can be copied to another cell where trying to copy the direct-linked cell only copies the value, not the link. If I have a lot of cells all linked to a particular cell in another…
-
Duplicate rank what formula would return the most recent week?
I have a formula to return the date of the 5 highest ranking weeks. Two weeks are both ranking as the 5th week as they both have the same result. Currently it is returning the oldest of the two dates. Is there a way to get the most recent week instead?
-
Add a number of months to a date
Hello I have a field "laste due date" which contains a date, I would like to add to this date a number of months defined in the column "FREQUENCY (month)" so that returns the date incremented in the column "TEST", I have several formulas without success, it's a pity that there is not an equivalent formula in excel with the…
-
Return values from a sheet
Hello, I have a sheet with the following columns: Recordable (yes/no) Date Description This sheet has a list of all injuries . . many line items. In a summary sheet I want to return the "Date" from the most recent line item where "Recordable" = "Yes". On a separate line on the summary sheet, I want to return the…
-
Remove formulas in data shuttle
Data shuttle does not allow formulas to be turned off so when I upload an excel file in data shuttle, my numbers are being rounded.
-
Sumifs with criterion range in column with multiple entry options
I track all of our projects on a single sheet, to include opportunities and finished projects. I want to sum the total sold amount. I have a column called "project status" that has 11 options for various status descriptions (i.e. pipeline, proposed, awarded, booked, complete, etc.). I'm trying to use this column as the…
-
Counting rows from another sheet that meet a specific criteria
Hi All! I'm trying to count the amount of rows from another sheet that meet the following criteria: All rows that are within the "Quality Control - Bioassay" department, that are not closed nor cancelled in the status column and the creation date is less than 30 days, then less than 60 days and then more than 90 days…
-
Collect information from a changing database
I'm trying to find a way to search for information in a big database, but this database is constantly changing (new sheets imported with the API) The big database are split into 6 imported sheets (250 000 cells approximately on each sheet). This sheet is a list of products. I wanted to do a vlookup on these 6 sheets to…
-
JOIN(DISTINCT(COLLECT + JOIN(DISTINCT(COLLECT but how to remove duplicates due to using two formulas
Hi all, First post but I've been reading (and making use of!) many of the queries & answers for a while. Stuck on this one! I have two JOIN(DISTINCT(COLLECT formulas that I am adding together, but sometimes the same person's name will appear twice due to being pulled through by both formulas. How do I prevent this type of…
-
First Entry Flag equation as column formula?
Good morning! I'm trying to create a flag in my form receiver sheet to identify first entries vs. duplicates. This is to help me pull some metrics (counts) in other sheets. I have an equation that works row by row, but not in the very first row and also can't be converted to a column formula. What am I doing wrong? I'd…
