-
Cross sheet reference in a formula is not working
I am trying to change the status of a task when a row is added to a different sheet. I need 2 pieces of information to match, the course name and the module number. If those match, then I'd like the status to change to complete. Here's the formula that doesn't work: =IF(({Gen Ed CME/SDC/CME Support Team Mod... Range 1},…
-
Vlookup/Index/Contains HELP!!!
Hello, I have been trying for several days now to grab a contact name if there is a partial match on an address. I have one sheet that will have the address (sheet1) and I want it to pull the owner contact info from second sheet if the address is contained in the address field of sheet2 into sheet1 in the 'Who?' column.…
-
Formulae help with Harvey balls being auto populated based on %
Hi Team Can you please help confirm on how to write the formulae so that based on the certain percentage value the icons appear in the cell value eg. lets say between 60-80% its three quarter full harvey ball between 81% to 100% its a Full Harvey ball. I can work the range if you can drop in less than equal & greater than…
-
Unparseable error
Hi Team I’m getting this error with this formula =IF(OR(Area@row = "Grading", Area@row = "Flock"), (([Grading Records for Flock and Day]@row > 1, "Check for Double GRADING Entries"),IF([Flock Records for Flock and Day]@row > 1, "Check for Double Up FLOCK Entries"), IF([Grading Records for Flock and Day]@row < 1, "Alert:No…
-
Formula to tell me how many times a word shows up, but only for a specific Review Board
Here is what I need: Count how many times Strongly Agree shows up, but only when the Review Board is General Medicine TAG. I have this to give me the # of times Strongly Agree shows, how do I ensure I only get this for "General Medicine TAG": =COUNTIF({Survey Intake - TAG Post Survey Range 2}, HAS(@cell, "Strongly Agree"))
-
Adding an IF statement to a formula
I'm using the formula below to subtract numbers in two columns and provide the answer in a third. Some of the cells in the first column don't have numbers (there are words) so I am getting an #INVALID OPERATION error which I expected. I want to add an IF statement to the formula to return "0" in place of that error (not…
-
Formula to check a box based on multiple criteria
Hello! I need to check a box on 1 sheet based on 2 criteria from a second sheet, a serial number and a product name. I have tried using IF(AND( formulas as well as INDEX(COLLECT( but am getting an incorrect argument set error. Here is my latest attempt: =IF(INDEX(COLLECT({TCH MONTHLY TOTALS Range 4 serial #}, {TCH MONTHLY…
-
Row Number of a String in an array of cells
Columns G to M have email ids in several rows. I want to look up a specific email in columns G to M and find the row number. Is it possible?
-
Count if with dates
I am trying to count new hires for 2022. My formula is =countif({Hire Date},=>Date(01,01,2022)), but it comes back unparsable, any ideas?
-
join, collect with multiple entries
This formula in the "Also Notified" column works perfectly for one "Deputy" with one or more "Deputy Back-Up Contacts" =JOIN(COLLECT({Deputy Back-Up Contacts Range 4}, {Deputy Back-Up Contacts Range 1}, Deputy@row), ", ") However, when more than one Deputy is selected, the formula should return all the Back-Up Contacts for…