Jun 19 2023 12:51 PM
Jun 19 2023 12:51 PM
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!
Jun 20 2023 06:23 AMSolution
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:
as the following should be enough:
Jun 20 2023 10:58 AM
Jun 20 2023 02:22 PM
Jun 20 2023 09:01 PM
@L z. I think I found the issue, the data between the two sources seems to be formatted differently. For example, in one spreadsheet the ID # can be 6, but in the other sheet the same employee would be 00006 as our ID numbers are all listed as a 5 digit number from 00001-99999. I think my new problem is that both of these two sources are unable to be adjusted by me as they come out of data lakes. Is there any way to have the MATCH function still work for these?
source 1 - source 2:
00006 = 6
00354 = 354
01000 = 1000
12345 = 12345
Jun 20 2023 11:16 PM
If Source1 (your Master as I understand) is an Excel file where the Employee ID are always 5 digits with leading zeros it's highly probable those IDs are stored a Text values in which case the following does it here:
=IF(ISNUMBER(MATCH(TEXT([@EmployeeID],"00000"), 'PathToYour[Master.xlsx]Sheet1'!$A:$A, 0)), "y", "")
Jun 21 2023 06:51 AM