-
Lookup To Return Multiple Results — Using JOIN, COLLECT
Hello, I am trying to use JOIN, COLLECT to return multiple results but just can't seem to get it to work. This is what I have: =JOIN(COLLECT([Column111]:[Column111], [Column7]:[Column7], [Column1]@row, "N/A")) [Column111]:[Column111] is the range of the data I want to be returned [Column7]:[Column7] is the range of where…
-
advanced formula help!
Hello! I am trying to create an advanced type formula and am having issues. Simple need: I want to know the platform in each quarter. The platform can be one of 5 choices. So using the summary info tab, I want to create formulas to do this. the basic logic is, If Quarter = QTR 1, count "FM - on24 - Automatic lead…
-
Filling Columns based on a Selection From Dropdown
I am working on a formula and cannot get it to return correctly, I need to be able to select one of two options in a dropdown. I was using a vlookup and kept getting the N/A for all of them even if the condition was met. I think I missed an additional trigger. Regions: ABC and XYZ for the example. Based on which region…
-
Contact List formatting from formula only showing email address
I've got a formula that pulls contacts from another reference sheet into a Contact List column, but it also checks an adjacent column for a value to transpose a would-be contact: =IFERROR(IF(Resource@row = "Eric R.", "erussell@email.com", INDEX(COLLECT({IT Requisition Tasks Resource}, {IT Requisition Tasks Task Name},…
-
Using NOT and OR functions in Sumifs
I am trying to create a sumifs function that sums up all of the expenses in a second sheet if they are not labeled "travel". I attempted it with an all-inclusive OR function and then tried it with the NOT function and neither seem to work. It seems that they need to look at cells specifically within the sheet and can't…
-
Index and match doesn't always work
Hi, I was wondering if anyone had come across a technical issue with index and match or if it's something I am doing wrong (the formula is definitely correct). I have a critically important index and match on a large number of sheets but it has stopped working. Replacing it with vlookups work so I have a fix but I'd rather…
-
Sum Hours in a column by row name
Formula>> =SUMIF([Assigned To]:[Assigned To], [Assigned To]@row, [Hours Testing]:[Hours Testing]) Formula works if only on a single row. eg will produce the sum 3 next to "Armida" When i copy the formula it reads Circular Reference. *Goal is NOT to type name in formula "Armida". I'd like it to reference the row Assigned To…
-
Sheet Summary field suddenly started to append date with time of day
A Sheet Summary field that concatenates the next scheduled task name and due date suddenly began this week to append the date (MM/DD/YY) with the time of day (HH:MM). No changes were made to formula. Example: Formula: =IFERROR(INDEX(COLLECT([Task Name]:[Task Name], Actual:Actual, ISBLANK(@cell), Status:Status, "In…
-
How to Identify Skipped Number
I have a column containing document numbers that looks somethin like this: s-001 s-002 s-004 Does anyone know of a formulas that I could put in the sheet summary to return the first skipped number (in this case, s-003)? I know how to do this with a "helper column", but I'd like to avoid that if possible.
-
How do I create a ONE to MANY mapping cross-sheet formula/mapping?
I have a sheet called a "Catchall" which is the FULL list of projects being worked on in the company, and for every project on the list, there is a related OKR, start date, end date, health score etc I need to be able to pull all related projects that share a COMMON objective into a different sheet. The goal being to be…