Feb 04 2022 05:06 PM
Hello All,
I hope nothing too similar has been posted before, but I am struggling with a filter function.
I have two sheets, One where data will be displayed to the end user, and a query that contains the information to be fetched. I am trying to have a section auto-complete with Employee Names, where employee cost center is XXXXX and Employee Job Title is EITHER Production Team Member OR Production Temporary. The filter is mostly working, but I am getting results for all job titles in the cost center (so, it is also pulling supervisors). I am using the below formula to accomplish this.
=FILTER(INDEX(HCData!A:G,SEQUENCE(ROWS(HCData!A:G)),{4,7}),(HCData!A:A=$A$13)*OR(ISNUMBER(SEARCH("Production Temporary",HCData!C:C)),ISNUMBER(SEARCH("Production Team Member",HCData!C:C))),)
I originally tried using boolean argument for the OR condition, but that didn't work so I tried an OR function.
Unfortunately, I'm not able to provide the spreadsheet due to the nature of the data. My query columns are: "Cost Center" "ID Number" "Job Title" "Name" "Shift" "Job Code" and "Job Code Abbreviated (conditional column)".
Please let me know if any additional information would be helpful. I do have another cell using formula: =FILTER(INDEX(HCData!$A:$G,SEQUENCE(ROWS(HCData!$A:$G)),{4,7}),(HCData!$C:$C="Production Team Leader")*(HCData!$A:$A='DAT1 Org Chart (1st)'!$A$13),)
This formula is only returning the correct job title, so I know that the issue lies somewhere in adding a 3rd condition.
Feb 04 2022 06:02 PM
Solution=FILTER(INDEX(HCData!A1:G50;SEQUENZ(ZEILEN(HCData!A1:G50));{4.7});(HCData!A1:A50=A13)*((HCData!C1:C50="Production Team Member")+(HCData!C1:C50="Production Temporary"));)
This formula works in my spreadsheet.
Feb 04 2022 06:41 PM
Feb 07 2022 12:08 PM
@harshulz Yes, that's correct. I have a query on one sheet and will be filtering that data on the sheet presented to end users.
Feb 07 2022 12:10 PM
Feb 07 2022 12:24 PM
Feb 07 2022 12:45 PM
The formula returns the values from columns 4 and 7 of sheet "HCData". I deleted all the data in column 4 (Name) of sheet "HCData" and the formula returns only "0" for this column as you can see in the attached file. Maybe this is what happens in your file.
Feb 07 2022 12:48 PM
Feb 07 2022 12:54 PM
Can you attach a file without sensitive data which shows the details that you can see in my attached files?
Feb 08 2022 09:38 AM
Feb 04 2022 06:02 PM
Solution=FILTER(INDEX(HCData!A1:G50;SEQUENZ(ZEILEN(HCData!A1:G50));{4.7});(HCData!A1:A50=A13)*((HCData!C1:C50="Production Team Member")+(HCData!C1:C50="Production Temporary"));)
This formula works in my spreadsheet.