Forum Discussion
Alternative to Countif due to closed workbooks
Hello all,
I am looking to see what alternative I can use to countif for the formula below. Essentially what I am doing is checking another workbook to see if an employee id # exists there and if it does, put "Y" as the result in this field. I removed the super long file location in the formula to make it easier on the eyes, and put "filelocation" in place as placeholder. Currently this formula works, but only if I have the other workbook open. I really need this function to be able to operate independently without the need for the other workbook open as it can be pretty large and I cannot include it in this workbook as a separate sheet.
=IF(OR(ISBLANK($L16)),"",IF(COUNTIF('filelocation/[MVR Monitoring Batch File (MASTER).xlsx]Sheet1'!$A:$A,[@EmployeeID]), "Y", ""))
I hope I explained this clear enough, and without the ability to post a picture of the spreadsheet I feel like this may be tough to understand. I am open to answer any questions that may assist. Thanks!
Hi pcarle1
An alternative could be MATCH:
=IF(ISNUMBER(MATCH([@EmployeeID], 'filelocation/[MVR Monitoring Batch File (MASTER).xlsx]Sheet1'!$A:$A, 0)), "y", "")Not quite sure I understand what the first portion of your complete formula is supposed to do:
=IF(OR(ISBLANK($L16)),"",...as the following should be enough:
=IF(ISBLANK($L16),"",...
Hi pcarle1
An alternative could be MATCH:
=IF(ISNUMBER(MATCH([@EmployeeID], 'filelocation/[MVR Monitoring Batch File (MASTER).xlsx]Sheet1'!$A:$A, 0)), "y", "")Not quite sure I understand what the first portion of your complete formula is supposed to do:
=IF(OR(ISBLANK($L16)),"",...as the following should be enough:
=IF(ISBLANK($L16),"",...- Hello and thank you for the response. I totally forgot about utilizing the match function for this. So far I have not gotten it to populate a "Y" when I know it should, but I have been so swamped today I have not had the chance to really dive in it. If I have more issues with it, I will respond with what I learn.
In response to the first portion, this was created as a way to return the value "" if no value was found in cell $L16. I had been accustomed to putting IF(OR(ISBLANK for a while as this was something I learned not realizing the OR( is not needed,
