Challenge with Array

[mattyblueice]

Good Morning I have a challenge with an array that I am working on - I searched through the forums and my solution should work but its not.

I am working to create a report that has a data validation list of Companies and when the user selects a company from the drop down all of the employees working on that company will populate. The data source has Company, Employees, Month and the Hours worked by that employee in that month. Here is a layout of what the data source looks like, the report setup and the formula I am using (I am doing Cntrl, Shift, Enter for the Array). I am getting all blanks and I am not sure what is the issue. Any ideas? Thanks so much

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Also why is your formula looking at 3 different sheets?

 

[mattyblueice]

This is on Microsoft Excel for Mac and I updated the formulas to correctly reference the setup example

 

[Fluff]

Yes but which version are you using 2016,2019,365 or something else?

 

[mattyblueice]

 

[Fluff]

Ok, you have 365 so I suggest that you update your profile to show that & the fact that you are using a Mac.

 

[Fluff]

How about

=FILTER(Sheet7!B:B,Sheet7!A:A=F2)

although it looks as though the drop down is more like F5 than F2

 

[mattyblueice]

I actually tried the Filter function too but all I got was #N/A both with enter and Ctrl/Shift/Enter. The dropdown cell reference is correct.

 

[Fluff]

With 365 you no longer need to use Ctrl Shift Enter for any formula.
The #N/A suggests that you have those errors on Sheet7 col B

 

[mattyblueice]

Interesting so I did some trouble shooting on the Filter function - the column that has the Company data is a vlookup formula as company data is listed as an ID. So I thought maybe the formula is preventing the Filter from working so I changed the dropdown to company ID and reference the column that has ID and I added the exact range and it works but it brings in every instance that the employee is listed. I added Unique to the filter and it worked so

=UNIQUE(FILTER(Sheet7!B2:B1236,Sheet7!A2:A1236=F2))

Thanks for the help on Brainstorming this out Fluff!